I have performance issues if I do the following pseudo-query:
SELECT a, b, (SELECT count(*) FROM t2 WHERE something) AS c FROM t1 ORDER BY a LIMIT 10;
After some tests, it seems to me that the subquery on t2 is computed for all rows of t1. As I don't "ORDER BY c", there is no need to compute c for every row. I know I can (or should ?) work with joins or with a subquery in the from clause, but I'd like to make sure there is no other way before changing my sqls.
A subjective reason for me to prefer subqueries in fields instead of joins of sub tables is that, when it only relates to the text displayed, it is easyer to read and to change, and I mess less with agregate functions.