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.

  • Welcome to the site. Did you try the DATETIME in where clause. – Md Haidar Ali Khan Dec 26 '18 at 16:55
  • What is O.S environment? – Md Haidar Ali Khan Dec 26 '18 at 17:01
  • Please provide SHOW CREATE TABLE. We need to see the indexes, the datatypes of other columns in the WHERE, etc. – Rick James Dec 26 '18 at 23:56

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.

  • An Index on page_id AND created_at worked, thanks! (now apx. 500ms) – Idan Ptichi Dec 26 '18 at 19:39

Indexes needed:

page_views:  (created_at, page_id)  -- in this order
pages:       (id)   -- probably already the PRIMARY KEY?
embed_codes: (id)   -- probably already the PRIMARY KEY?

If there are still issues after adding those indexes, please provide EXPLAIN SELECT ... and SHOW CREATE TABLE.

indexing cookbook

  • thank, an index on created_at & page_id did solved the issue – Idan Ptichi Dec 27 '18 at 8:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.