CODEWITHSUNDEEP
mysqlindexing
Adrian
Adrian

Reputation: 2012

MySQL Index for timestamp not improving performance

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

Answers (1)

lefred
lefred

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

Related Questions