MySQL query taking too long to execute

index-tuningMySQLphpmyadmin

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

Best Answer

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.

Related Solutions

MySQL – Why is This Simple Join Taking So Long?

Look at the original query

select count(page_views.id) as views
from playpack_media
join page_views on page_views.itemId=playpack_media.media_Id
where playpack_media.playpack_id = 1
    and page_views.started_at BETWEEN '2014-06-23' and '2014-07-07'

You should do a couple of things.

First, create to compound indexes

ALTER TABLE page_views     ADD INDEX started_at_itemid_ndx    (started_at,itemid);
ALTER TABLE playpack_media ADD INDEX playpack_id_media_Id_ndx (playpack_id,media_Id)

Try to refactor the query

SELECT COUNT(1) as views FROM
(
    SELECT media_Id FROM playpack_media
    WHERE playpack_id = 1
) A INNER JOIN
(
    SELECT itemId FROM page_views WHERE
    started_at BETWEEN '2014-06-23' and '2014-07-07'
) B ON A.media_Id = B.itemId;

Perhaps breaking down the views by ItemID

SELECT IFNULL(A.media_id,'Total') Media,COUNT(1) as views FROM
(
    SELECT media_Id FROM playpack_media
    WHERE playpack_id = 1
) A INNER JOIN
(
    SELECT itemId FROM page_views WHERE
    started_at BETWEEN '2014-06-23' and '2014-07-07'
) B ON A.media_Id = B.itemId
GROUP BY A.media_id WITH ROLLUP;

Mysql – How to optimized thesql query having large dataset

First, if email_id and delivery_id together are a unique key, please add a primary -composite- key on both tables for (email_id, delivery_id)

Second, the concat is not necessary and will prevent the previous key from being used. Try:

SELECT ol.* 
FROM open_log ol 
JOIN sent_log sl 
ON (ol.email_id, ol.delivery_id) = (sl.email_id, sl.delivery_id)

Best Answer

Related Solutions

MySQL – Why is This Simple Join Taking So Long?

Mysql – How to optimized thesql query having large dataset

Related Question