stop optimize table`?

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View
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'@'localhost'
to database 'information_schema' 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)


Re: stop optimize table`?


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

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


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?


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).

Quoted text here. Click to load it

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.


Site Timeline