I have a large table with a timestamp column and it holds a very large amount of data (a few million rows).
Whenever running a filtered query by range on the timestamp column for this table, depending on the size of the range, it takes a considerable amount of time (2+ seconds). This column has an index created for it which would lead me to believe that the query return should be faster.
I noticed that by destroying the index and re-adding it, the query performs easily 40% better.
Bellow is a simple example of how the table is structured:
create table foo ( id int(11) not null auto_increment, fk_id int(11) not null, datecolumn timestamp, primary key (id, fk_id), key idx_timestamp (datecolumn) constraint fk_fk_id foreign key (fk_id) references bla (id) ) engine = InnoDB;
Any reason why the re-creation of the index would make the query run faster and how can I make this as part of a maintenance ? I understand that MySQL InnoDB default index is BTree and it can get messy but it should have a way to optimize or re-organize these so the query performance is not so bad.
Note: I have ran
optimize table foo; flush tables;
And these didn't seem to solve anything related to the index.
When you use explain it shows some indexes being used but mainly, it shows the type ranged and under this type that there is most of number of rows analyzed to return just a few of them.
fk_id is part of the where clause, needs to be, it is also part of a composite key which means that it is deterministic to return the results.
The query is basically this:
select id, fk_id, datecolumn, bar.id, bar.name, bar.type from foo join bar on bar.id = foo.fk_id where foo.datecolumn between '2016-01-01 05:00:00' and '2017-01-01 04:59:59' and foo.fk_id in (1, 2, 4)
Even if the query was a simple select where datecolumn between range I get the same result.