x IN ( SELECT ... )
turn into a
When possible, avoid
Do not 'hide' an indexed column in a function, such as
WHERE DATE(x) = ...; reformulate as
WHERE x = ...
You can generally avoid
WHERE LCASE(name1) = LCASE(name2) by having a suitable collation.
Do no use
OFFSET for "pagination", instead 'remember where you left off'.
SELECT * ... (unless debugging).
Note to Maria Deleva, Barranka, Batsu: This is a place holder; please make remove these items as you build full-scale examples. After you have done the ones you can, I will move in to elaborate on the rest and/or toss them.