Some mysql query optimization DISTINCT,GROUP BY, etc :)

Our previous developer use standard SQL guide to create query for selection “top 50 referrers”

SELECT referrer  FROM referrers_log WHERE (create_date = curdate() OR create_date=curdate()-1) AND site_id = 123  GROUP BY referrer ORDER BY SUM(views_count) DESC LIMIT 50;

50 rows in set (26.31 sec)

It significantly loads our database;

Rewrite query for using temporary table with distinct referrer:

DROP TEMPORARY TABLE REF;
CREATE TEMPORARY TABLE REF AS (SELECT DISTINCT referrer FROM referrers_log WHERE (create_date = curdate() OR create_date=curdate()-1) AND site_id = 123);
SELECT REF.referrer,SUM(views_count) FROM referrers_log,REF
WHERE referrers_log.referrer=REF.referrer
AND (create_date = curdate() OR create_date=curdate()-1) AND site_id = 123
GROUP BY REF.referrer ORDER BY SUM(views_count) DESC LIMIT 50;

50 rows in set (2.48 sec)

I feel happy 🙂 🙂 🙂

Leave a Comment


NOTE - You can use these HTML tags and attributes:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>