CODEWITHSUNDEEP
mysqlselect
Ben
Ben

Reputation: 25

How to simplify queries

How can I simplify this query? Can this query be simplified? I tried some joins but the results were not the same as this query below. Please give me some insights.

SELECT trafficbywebsite.`adwordsCampaignID`, 
trafficbywebsite.adwordsAdGroupID, trafficbywebsite.adPlacementDomain, 
trafficbywebsite.counts traffic, convertedtrafficbywebsite.counts 
convertedclicks
FROM
    (
        SELECT `adwordsAdGroupID`, `adPlacementDomain`, COUNT(*) counts
        FROM
            (
                 SELECT GA_entrances.*
                 FROM 
                    GA_entrances,
                    GA_conversions
                    WHERE 
                    GA_entrances.clientId=GA_conversions.clientId
                    AND (eventLabel='myurl' OR eventLabel='myotherurl')
                AND YEAR(GA_entrances.timestamp)>=2016 
                AND MONTH(GA_entrances.timestamp)>=6
                AND YEAR(GA_conversions.timestamp)>=2016 
                AND MONTH(GA_conversions.timestamp)>=6                  
                GROUP BY GA_entrances.clientId
                 ) clickers
               GROUP BY `adwordsAdGroupID`, `adPlacementDomain`
        ) convertedtrafficbywebsite
       ,(
       SELECT `adwordsCampaignID`, `adwordsAdGroupID`, adPlacementDomain, 
      COUNT(*) counts
      FROM 
        GA_entrances
      WHERE
        YEAR(timestamp)>=2016 
        AND MONTH(timestamp)>=6
    GROUP BY `adwordsAdGroupID`, `adPlacementDomain`
     ) trafficbywebsite
     WHERE
     convertedtrafficbywebsite.counts>=(trafficbywebsite.counts/10)
     ORDER BY traffic DESC

Upvotes: 0

Views: 199

Answers (1)

Paul Maxwell
Paul Maxwell

Reputation: 35553

Without sample data it is difficult to be certain but it appears unlikely you can remove one of the subqueries. What you can do however is improve the way you flter for the dates. The thing to avoid is using functions on data to suit your filter criteria. For example you want data from 2016-06-01 onward, that is a single date, yet you are amending every row of data to match to a year and a month.

AND YEAR(GA_entrances.timestamp) >= 2016
AND MONTH(GA_entrances.timestamp) >= 6
AND YEAR(GA_conversions.timestamp) >= 2016
AND MONTH(GA_conversions.timestamp) >= 6
;

There is no need for all those functions, just compare to a single date:

AND GA_entrances.timestamp) >= '2016-06-01'
AND GA_conversions.timestamp >= '2016-06-01'
;

The other thing to avoid is using commas as a way to join tables. ANSI standard syntax for this 25+ years old. This is the antique way of joining:

    FROM GA_entrances, GA_conversions
    WHERE GA_entrances.clientId = GA_conversions.clientId

This is considered best practice:

    GA_entrances.*
    FROM GA_entrances
    INNER JOIN GA_conversions ON GA_entrances.clientId = GA_conversions.clientId

Upvotes: 2

Related Questions