CODEWITHSUNDEEP
mysqlperformanceselectjoin
waxical
waxical

Reputation: 3896

MySQL - Fastest way to select relational data avoiding left join

I've currently got a query that selects metrics data from two tables whilst getting the projects to query from two other tables (one is owned projects, the other is projects to which the user has access).

SELECT v.`projectID`,
(SELECT COUNT(m.`session`) 
    FROM `metricData` m
    WHERE m.`projectID` = v.`projectID`) AS `sessions`,
(SELECT COUNT(pb.`interact`)
    FROM `interactionData` pb WHERE pb.`projectID` = v.`projectID` GROUP BY pb.`projectID`) AS `interactions`
FROM `medias` v
LEFT JOIN `projectsExt` pa ON v.`projectsExtID` = pa.`projectsExtID`
WHERE (pa.`user` = '1' OR v.`ownerUser` = '1')
GROUP BY v.`projectID`

It takes too long, 1-2seconds. This is obviously the multi left-join scenario. But, I've got a couple of ideas to improve speed and wondered what the thoughts were in principle. Do I:-

Upvotes: 2

Views: 3786

Answers (2)

Roland Bouman
Roland Bouman

Reputation: 31991

There's two questions here:

  1. how can I get my result in less than 2 seconds
  2. how can I avoid a left join.

To answer #1 properly there has to be more information. Technical information, such as the explain plan for this particular query is a good start. Even better if we'd have the SHOW CREATE TABLE of all tables that you access, as well as the number of rows they contain.

But I'd also appreciate more functional information: what exactly is the question you're trying to answer? Right now, it seems you're looking at two different sets of medias:

  1. either there is no matching row in projectsExt, in which case medias.ownerUser must equal '1' (is that '1' supposed to be a string btw?)
  2. or there is exactly one mathching row in projectsExt for which projectsExt.user must equal '1' (is that '1' supposed to be a string btw?)

By lack of enough information to answer #1, I can answer #2 - "how to avoid a left join". Answer is: write a UNION of the two sets, one where there is a match and one where there isn't a match.

SELECT v.`projectID`
,      (
       SELECT COUNT(m.`session`) 
       FROM `metricData` m
       WHERE m.`projectID` = v.`projectID`
       ) AS `sessions`
,      (
       SELECT COUNT(pb.`interact`)
       FROM `interactionData` pb
       WHERE pb.`projectID` = v.`projectID`
       GROUP BY pb.`projectID`
) AS `interactions`
FROM ( 
    SELECT      v.projectID
    FROM medias 
    WHERE ownerUser = '1'
    GROUP BY projectID
    UNION ALL
    SELECT      v.projectID
    FROM medias v
    INNER JOIN projectsExt pa
    ON         v.projectsExtID = pa.projectsExtID
    WHERE      v.ownerUser != '1'
    AND        pa.user = '1'
    GROUP BY v.`projectID
) v

Upvotes: 4

Naltharial
Naltharial

Reputation: 2152

Have you tried, instead, to refactor everything into left joins? Seeing as how you're always grouping on the same field, it shouldn't be a problem. Try that and post an EXPLAIN to see what the bottlenecks are.

Subselects are less performant than joins, because the engine can optimize the joins to a much higher degree. In fact, subselects will usually, where applicable, be rewritten into joins by the engine where possible.

As a rule of a thumb, there is no gain in splitting queries, all you gain is overhead and confusing the optimizer. There are, as always, exceptions to this rule, but they come into play after you've done what you can traditionally and know you keen such an approach.

Upvotes: 0

Related Questions