1

My MySQL query is taking around 20 minutes to execute which I feel is excessive.

SELECT 
  hyp_id,MAX( player_count ) AS MAXPLAYERCOUNT
  , MAX( viewer_count ) AS MAXVIEWERCOUNT
  , SUM( player_count ) AS TOTPLAYER
  , SUM( viewer_count ) AS TOTVIEWER 
FROM player_count_log 
WHERE 
  DATE(added_time) = CURDATE() 
GROUP BY hyp_id 
ORDER BY  hyp_id

The player_count_log table contains over 63 million records with ~18k added every hour.

Here are screenshots of db and indexes

screenshot of the database

screenshot of the indexes

2
  • 1
    Lots of indexes, did you check if an index is actually used? First I would rewrite the WHERE: WHERE added_time >= CAST(CURDATE() AS TIMESTAMP), you probably don't need AND added_time < CAST(CURDATE() + 1 AS TIMESTAMP) as there should be no future rows.
    – dnoeth
    Jan 21, 2016 at 7:45
  • Use EXPLAIN to see the used indexes and other information about the query plan.
    – jkavalik
    Jan 21, 2016 at 8:06

1 Answer 1

1
DATE(added_time) = CURDATE() 

is the root of some of the evil

Do not "hide" a column (added_time) inside a function (DATE) if want an index to be used.

If added_time is a DATE, then you can simply do

added_time = CURDATE()

If it is a DATETIME or TIMESTAMP, then do

added_time >= CURDATE() AND
added_time  < CURDATE() + INTERVAL 1 DAY

Then your INDEX(added_time) can be used.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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