stop optimize table`?

[Sonnich]

While trying to shorten my files, I tried optimize table - later I
found that I need:

mysqlcheck -u root -p[password[ --all-databases --analyze --optimize
(thanks to Markus Popp).

But it gives this error:

mysqlcheck: Got error: 1044: Access denied for user 'root'@'localho st'
to database 'information_sc hema' when executing 'OPTIMIZE TABLE ... '

How can I stop this?
The optimise command seem to be in the way (and since I started it, my
computer has got pretty slow)

S

[Markus Popp]

Re: stop optimize table`?

Hi,

that's a bug, but it should be solved in MySQL 5.0.16. You can use this
workaround:

mysqlcheck -u root -p --databases [database1, database2,
....] --analyze --optimize

Markus

[Sonnich]

Re: stop optimize table`?

Sorry for being a newbie/stupid here, but...

I ran that, it worked - but my DB grew from 1 GB to 1,18 GB (and I have
deleted 2 tables lately, it was 740 Mb).

Aften that I tried to run the previous recommended command:
mysqlcheck -u root -p[password] --all-databases --analyze --optimize

with the same error message as always.

Should my DB have become smaller by this?

S

[Markus Popp]

Re: stop optimize table`?

If you use a MySQL version before 5.0.16, you should specify the databases
one by one (due to that bug that's solved in 5.0.16).
[color=blue]
> Should my DB have become smaller by this?[/color]

If there are tables that are not optimized (for example because there are
'holes' in the files from data that has been deleted), it should.

Running this command from time to time has these 2 advantages:

* the data files are defragmented, so they can be read slightly faster and
consume less disk space
* the index statistics are re-calculated, so the query optimizer can make
better decisions on how to execute a query.

Markus