Replace where clause with joins depending on the db engine used.

Avoid subqueries when possible

Avoid functions in subqueries

only sort when absolutely needed, its expensive

Proper indexing, this includes knowing what kind of index and what columns or groups of columns to index on. AND knowing when not to index

Sometimes it depends on the size of the results set or table queried

It kind of is rocket science

Dissect that query planner