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
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.
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.