Syntax
- Don’t use DISTINCT and GROUP BY in the same SELECT.
- Don’t paginate via OFFSET, “remember where you left off”.
- WHERE (a,b) = (22,33) does not optimize at all.
- Explicitly say ALL or DISTINCT after UNION – it reminds you pick between the faster ALL or the slower DISTINCT.
- Don’t use SELECT *, especially if you have TEXT or BLOB columns that you don’t need. There is overhead in tmp tables and transmission.
- It is faster when the GROUP BY and ORDER BY can have exactly the same list.
- Don’t use FORCE INDEX; it may help today, but will probably hurt tomorrow.
Remarks
See also discussions about ORDER BY, LIKE, REGEXP, etc. Note: this needs editing with links and more Topics.
Cookbook on building optimal indexes.