MYSQL Have an INDEX
The most important thing for speeding up a query on any non-tiny table is to have a suitable index.
WHERE a = 12 --> INDEX(a)
WHERE a > 12 --> INDEX(a)
WHERE a = 12 AND b > 78 --> INDEX(a,b) is more useful than INDEX(b,a)
WHERE a > 12 AND b > 78 --> INDEX(a) or INDEX(b); no way to handle both ranges
ORDER BY x --> INDEX(x)
ORDER BY x, y --> INDEX(x,y) in that order
ORDER BY x DESC, y ASC --> No index helps - because of mixing ASC and DESCSubqueries
Subqueries come in several flavors, and they have different optimization potential. First, note that subqueries can be either "correlated" or "uncorrelated". Correlated means that they depend on some value from outside the subquery. This generally implies that the subquery must be re-evaluated for each outer value.
This correlated subquery is often pretty good. Note: It must return at most 1 value. It is often useful as an alternative to, though not necessarily faster than, a LEFT JOIN.
SELECT a, b, ( SELECT ... FROM t WHERE t.x = u.x ) AS c
FROM u ...
SELECT a, b, ( SELECT MAX(x) ... ) AS c
FROM u ...
SELECT a, b, ( SELECT x FROM t ORDER BY ... LIMIT 1 ) AS c
FROM u ...This is usually uncorrelated:
SELECT ...
FROM ( SELECT ... ) AS a
JOIN b ON ...Notes on the FROM-SELECT:
.If it returns 1 row, great.
.A good paradigm (again "1 row") is for the subquery to be ( SELECT @n := 0 ), thereby initializing an `@variable for use in the rest or the query.
.If it returns many rows and the JOIN also is ( SELECT ... ) with many rows, then efficiency can be terrible. Pre-5.6, there was no index, so it became a CROSS JOIN; 5.6+ involves deducing the best index on the temp tables and then generating it, only to throw it away when finished with the SELECT.
JOIN + GROUP BY
A common problem that leads to an inefficient query goes something like this:
SELECT ...
FROM a
JOIN b ON ...
WHERE ...
GROUP BY a.idFirst, the JOIN expands the number of rows; then the GROUP BY whittles it back down the the number of rows in a. There may not be any good choices to solve this explode-implode problem. One possible option is to turn the JOIN into a correlated subquery in the SELECT. This also eliminates the GROUP BY.