|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
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?
select ticker, id, lastvalue, (select count(*) from transactions 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