OPTIMIZE TABLE vs. myisamchk
- Date: Wed, 1 Jul 2015 14:11:49 -0400
- From: Larry Martell <larry.martell@xxxxxxxxx>
- Subject: OPTIMIZE TABLE vs. myisamchk
I have a very large table (~50GB) and periodically rows are purged
from it and I want to run OPTIMIZE TABLE to recover the space. But I
do not have enough space to run it. If I do run it the server hangs
and must be killed and restarted and the table is damaged and must be
repaired. I do this with myisamchk.
At https://dev.mysql.com/doc/refman/5.1/en/myisam-optimization.html I read this:
To coalesce fragmented rows and eliminate wasted space that results
from deleting or updating rows, run myisamchk in recovery mode:
shell> myisamchk -r tbl_name
You can optimize a table in the same way by using the OPTIMIZE TABLE
SQL statement. OPTIMIZE TABLE does a table repair and a key analysis,
and also sorts the index tree so that key lookups are faster. There is
also no possibility of unwanted interaction between a utility and the
server, because the server does all the work when you use OPTIMIZE
Does this mean that myisamchk -r -a -S does the same thing as OPTIMIZE TABLE?
If they do do the same thing, why does myisamchk work but OPTIMIZE
TABLE run out of space?
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql