Which Query of JOIN with DISTINCT & JOIN with Subquery has better Performance?

Posted on

Question :

In the following two queries: http://sqlfiddle.com/#!9/081f9e/11

SELECT a.id,
       g.names as gamma,
       o.names as omega
FROM alpha a
     JOIN (SELECT ao.alpha_id,
               GROUP_CONCAT(o.name) AS names
           FROM alpha_omega ao
               JOIN omega o on o.id = ao.omega_id
           GROUP BY ao.alpha_id ) o ON o.alpha_id = a.id
     JOIN (SELECT ag.alpha_id,
               GROUP_CONCAT(g.name) AS names
           FROM alpha_gamma ag
               JOIN gamma g on g.id = ag.gamma_id
           GROUP BY ag.alpha_id ) g ON g.alpha_id = a.id
WHERE a.id = 1;

SELECT a.id, 
       GROUP_CONCAT(DISTINCT g.name) AS gamma, 
       GROUP_CONCAT(DISTINCT o.name) AS omega 
FROM alpha a
     JOIN alpha_omega ao ON ao.alpha_id = a.id 
     JOIN omega o ON o.id = ao.omega_id 

     JOIN alpha_gamma ag ON ag.alpha_id = a.id 
     JOIN gamma g ON g.id = ag.gamma_id
WHERE a.id = 1
GROUP BY a.id;

Which one of them is better to be used to achieve the optimal performance and efficiency?

The First one uses JOIN with SubQueries while the Second one uses JOIN with DISTINCT, In the First one I get exactly 8 values out from gamma & omega columns,

But in the Second one, I get duplicated values up to the double, Which makes me get 16 value, Then I use DISTINCT to make it 8 values

Keeping in mind that I’ve set the correct INDEXes to achieve the maximum speed from INDEX.

Which is better to be used on the long-term exactly?

Let’s say I have 50,000 record in each Table, And the Query is executed 20,000 time per Sec.

Answer :

The first one is good, when you select a larger range, the engine should no longer treat the “subqueries” as such, but run them independantly, and join the results.

Leave a Reply

Your email address will not be published. Required fields are marked *