Description:
optimize table just returns ok without any work done, if it believes that the table is already optimized.
This is, if the current data file is gap-free.
However optimize does not only remove gaps from the data file, but also re-orders the index. Which leads to more accurate estimation in the optimizer.
In the how to repeat, a table is created and filled with insert only.
run the explain, and note the rows-expected. Try optimizing, nothing will change.
Add and delete a row, now the optimize will work, and the rows expected will change too.
How to repeat:
drop table if exists i1;
create table i1 (a int not null, b int not null, index (a,b));
insert into i1 values (1,2), (1,3), (2,3), (2,4);
insert into i1 select a+5, b*2 from i1;
insert into i1 select a+5, b*2 from i1;
insert into i1 select a+5, b*2 from i1;
insert into i1 select a+5, b*2 from i1;
insert into i1 select a+5, b*2 from i1;
insert into i1 select a+5, b*2 from i1;
insert into i1 select a+5, b*2 from i1;
analyze table i1;
explain select * from i1 where a in (11,12,16,17,21,22,26,27,31,32,36,37) and b in (8,12,16,24,32,48,64,96,128,192,256);
optimize table i1;
explain select * from i1 where a in (11,12,16,17,21,22,26,27,31,32,36,37) and b in (8,12,16,24,32,48,64,96,128,192,256);
insert into i1 select 555,555; delete from i1 where a = 555;
optimize table i1;
explain select * from i1 where a in (11,12,16,17,21,22,26,27,31,32,36,37) and b in (8,12,16,24,32,48,64,96,128,192,256);
Suggested fix:
Maybe Optimize could keep the old datafile and only rebuild the index. In any case, it should at least execute, even if the table had only inserts.
