Avoid inefficient constructs

x IN ( SELECT ... )

turn into a JOIN

When possible, avoid OR.

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’.

Avoid 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.