CODEWITHSUNDEEP
mysql
Schalk Keun
Schalk Keun

Reputation: 548

Slow MySQL query using LEFT JOIN

I'm using a simple left join query to fetch two rows of data from two separate tables. They both hold a common column named domain and I join them on this column to calculate a value based on the one tables visits and the other tables earnings.

SELECT t1.`domain` AS `domain`,
       (SUM(earnings)/SUM(visits)) AS `rpv` 
FROM   hat_adsense_stats t1 
LEFT JOIN hat_analytics_stats t4 ON t4.`domain`=t1.`domain`  
WHERE(t1.`hat_analytics_id`='91' OR t1.`hat_analytics_id`='92') 
       AND t1.`date`>='2013-02-18' 
       AND t4.`date`>='2013-02-18' 
GROUP BY t1.`domain` 
ORDER BY rpv DESC 
LIMIT 10;

this is the query i run and it takes 9.060 sec to execute.

The hat_adsense_stats table contains 60887 records The hat_analytics_stats table contains 190780 records

but by grouping by domain it returns 186 rows of data that needs comparing.

Any suggestions on in-efficient code or on better way to resolve this will be appreciated!

Upvotes: 1

Views: 95

Answers (3)

Schalk Keun
Schalk Keun

Reputation: 548

thanks raheel for opening the door, this is what worked in the end, with a execution time of 0.051sec. :)

SELECT 
    t1.`domain` AS `domain`, 
    SUM(earnings)/visits AS `rpv` 
FROM hat_adsense_stats t1 
    INNER JOIN (SELECT 
        domain, 
        SUM(visits) AS visits 
        FROM hat_analytics_stats 
        WHERE `date` >= "2013-02-18" 
        GROUP BY domain) AS t4 
    ON t4.domain = t1.domain  
WHERE t1.`hat_analytics_id` IN('91','92') 
AND t1.`date`>='2013-02-18' 
GROUP BY t1.`domain` 
ORDER BY rpv DESC 
LIMIT 10

Upvotes: 1

Kickstart
Kickstart

Reputation: 21533

The LEFT JOIN is unnecessary as you check the value of an item from the right side of the join. An INNER JOIN would work just as well here and might well be quicker

Upvotes: 0

Muhammad Raheel
Muhammad Raheel

Reputation: 19882

Change your query like this

SELECT
  t1.`domain`           AS `domain`,
  t2.earnings/t2.visits AS `rpv`
FROM hat_adsense_stats t1
  INNER JOIN (SELECT
                domain,
                sum(earnings)          AS earnings,
                SUM(visits)            AS visits
             FROM hat_adsense_stats
             GROUP BY domain) AS t2
    on t2.domain = t1.domain
  LEFT JOIN hat_analytics_stats t4
    ON t4.`domain` = t1.`domain`
WHERE t1.`hat_analytics_id` IN('91','92')
    AND t1.`date` >= '2013-02-18'
    AND t4.`date` >= '2013-02-18'
GROUP BY t1.`domain`
ORDER BY rpv DESC
LIMIT 10;

Upvotes: 0

Related Questions