If you need a child table aggregate result for each row in a query, what's faster, putting the correlated aggregating subquery in the SELECT list, or in the FROM clause?
Thus, given tables stocks(id, user, ticker,lastvalue) and transactions(id, sid, action, qty, price, commission), and the requirement to retrieve stock values and per-stock transaction counts, which of these queries will run faster?
ticker, id, lastvalue,
where sid=stocks.id) N
... or ...
select s.ticker, s.id, s.lastvalue, t.N
from stocks s
select sid, count(*) N
group by sid
) t on s.id=t.sid;
The first query's syntax is simpler, so it's often the first choice. EXPLAIN reports that the second query requires examination of more rows than the first query does.
But benchmarks with caching turned off show that the second query is much faster--mainly because it executes one subquery rather than a subquery per row.
If you have a choice, put the aggregating subquery in the FROM clause.