MySql : Optimize Table query

Question

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.

Answer 1

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

Answer 2

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';