Is it possible to find out, by how much will the "Optimize" command be able to defragment a given table? I just want to find out this information beforehand.
1
- @Nikolas - surprisingly this difference increased after I ran optimize tablesimplfuzz– simplfuzz2010-01-08 11:53:09 +00:00Commented Jan 8, 2010 at 11:53
Add a comment |
2 Answers
I had some second thoughts on that answer :-) Actually its as easy as looking up the value - its all in information_schema.tables! Look up the field named data_free, it has the correct value in it and also behaves correctly in the examples I did:
use test; create table test1 as select * from information_schema.tables; alter table test1 engine = myisam; -- not even necessary select * from information_schema.tables where table_name = 'test1'\G delete from test1 limit 10; select * from information_schema.tables where table_name = 'test1'\G analyze table test1; select * from information_schema.tables where table_name = 'test1'\G optimize table test1; select * from information_schema.tables where table_name = 'test1'\G 
information_schema.tables always shows the correct data length, so you could compare the data_length field with a rough estimate of the needed data length (avg_row_length * table_rows), but only after you have updated the statistics with analyze table:
analyze table 'TABLE_TO_LOOK_UP'; -- to get row count etc. right SELECT table_name, concat( round((data_length - (avg_row_length * table_rows)) / 1024 / 1024, 2) , 'M' ) very_theoretical_size_difference_in_MB FROM information_schema.tables WHERE table_name = 'TABLE_TO_LOOK_UP'; 
- Surprisingly this difference increased after I ran optimize table !simplfuzz– simplfuzz2010-01-08 10:57:08 +00:00Commented Jan 8, 2010 at 10:57