heuristics

prefer uses indexes in general (especially for selection)
prioritze selections that narrow down the scope massively
join order should start with small tables
favor inner joins over cross joins to avoid excessive row combinations
minimise use of cross joins (Cartesian Product)
compare subqueries vs joins
apply limit/offset early
avoid full table scans
use indexes so sorting is optimised (so data is already sorted)
leverage parallel processing
evaluate query plans based on Cost based query optimisation
use indexes on join columns
eliminate redundant operations
use indexing or pre-aggregation for groupby/having
consider splitting queries with OR into separate SELECT statements and use union