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;
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 🙂 🙂 🙂
0 Comments.