0

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

#1ST QUERY
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;


#2ND QUERY
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.

1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.