In the most cases, small and simple points that can make huge improvement of performance - more than complicated SQL tricks. A little thinking is usually the best thing.
Here are some examples.
Don't do unnecessary JOINs!
Don't think that "it's better to bring more data - it can't make a damage" - it can! it can make more SQL work, more data, more calculations, etc.
Don't do LEFT JOIN where you can DO JOIN!
The explanation is simple: INNER JOIN looking for the match, and if it don't find it - it goes on, and LEFT JOIN don't filter, so it continue to scan the entire table. This causes that INNER JOIN uses indexes, etc. in order to be efficient and quick, what LEFT JOIN can't do.
As in the previous tip - don't think "it's better to bring more data - it can't make a damage".
Filter!
When it's required and when you can - filter (using Where clauses). Filter also can use indexes, etc. that wouldn't be used without it.
I improved performance of the query from 15 minutes to 1 second simply by add a WHERE clause. In most cases, the addition of this condition is also important both correct and logical - not only for performance.
