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
created_at, it takes only 700ms~, which is acceptable i guess, given the tables sizes.
page_views.created_at is indexed.
Any ideas why the WHERE clause is causing this slowness? Any way to avoid it? Thanks.