MySQL – WHERE Clause on TIMESTAMP Column Causes Performance Issues

countgroup byjoin;MySQLtimestamp

I have a pages and a page_views tables with 100k~ & 4M~ records respectively.

I run a query to get the top pages that have the most page_views:

    select `pages`.`id`, `pages`.`host`, `pages`.`embed_code_id`, `views`.`count` 
from `pages` 
inner join `embed_codes` on `pages`.`embed_code_id` = `embed_codes`.`id` 
inner join (
    select `page_id`, COUNT(*) AS count 
    from `page_views` 
    where `page_views`.`created_at` between '2018-12-25 13:20:35' and '2018-12-26 13:20:35' 
    group by `page_id`
    ) as views on `views`.`page_id` = `pages`.`id` 
where `embed_codes`.`id` in (512) 
and `embed_codes`.`account_id` = 173
order by `views`.`count` desc 
limit 6

This query takes more than 9 seconds to run, but when i remove the WHERE clause on page_views.created_at, it takes only 700ms~, which is acceptable i guess, given the tables sizes.

NOTE – page_views.created_at is indexed.

Any ideas why the WHERE clause is causing this slowness? Any way to avoid it?
Thanks.

Best Answer

It looks like with the WHERE condition the system is searching by the index on 'created_at' and when you eliminate this condition it searches by 'page_id'. if you add a new composed index on 'page_id' and 'created_at' or in the other order, the optimizer possibly will find the correct execution plan.

Related Question