We usually try to avoid subselects because sometimes they force the use of a temporary table and limits the use of indexes. But, when is good to use a subselect?
This example was tested over table a (1310723 rows), b, c and d ( 5 rows each) and with MySQL version 5.5 and 5.6.
Let’s suppose we have a query like this:
select a.name,sum(a.count) aSum,avg(a.position) aAVG,b.col1,c.col2,d.col3 from a join b on (a.bid = b.id) join c on (a.cid = c.id) join d on (a.did = d.id) group by a.name,b.id,c.id,d.id
What will MySQL do? First it will take the entire data set – this means that will go through each row …[Read more...]