Reputation: 2012
I have a table with 350,000, there are 240,000 records in the result of the following query
select count(*) as aggregate from `data`
where `block_id` = 6726
and created_at > '2000-01-01 00:00:00'
and created_at < '2199-01-01 00:00:00'
This is averaging at about 2 to 3 seconds.
Whereas
select count(*) as aggregate from `data` where `block_id` = 6726
is averaging at about .05 seconds.
I have tried putting an index on just created_at
, and also tried putting a combined one for created_at
and block_id
but nothing has made a difference.
Do you know why the performance is not improving with the index on the timestamp column? Is it related to the fact that I am using the > and < ?
Upvotes: 1
Views: 1856
Reputation: 129
Because you are using a range on the date field (created_at
), the second part of the combined index is not used.
I would recommend to make the combine index in the reverse order (block_id, created_at)
.
Then compare the Query Execution Plan (EXPLAIN <and the query>
).
Upvotes: 3