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.
See also discussions about ORDER BY, LIKE, REGEXP, etc. Note: this needs editing with links and more Topics.