Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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.

share|improve this question
    
@Nikolas - surprisingly this difference increased after I ran optimize table –  dharm0us Jan 8 '10 at 11:53
add comment

2 Answers

up vote 1 down vote accepted

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
share|improve this answer
add comment

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';
share|improve this answer
    
Surprisingly this difference increased after I ran optimize table ! –  dharm0us Jan 8 '10 at 10:57
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.