Question :
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 INDEX
es 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.