Most slow queries are fixed by the same handful of habits: read the plan, give the optimizer usable indexes and sargable predicates, and fetch less data.

1. Keep indexed columns bare

SQL

CREATE INDEX idx_events_created ON events (created_at);

EXPLAIN QUERY PLAN SELECT COUNT(*) FROM events WHERE strftime('%m', created_at) = '03';
EXPLAIN QUERY PLAN SELECT COUNT(*) FROM events WHERE created_at >= '2024-03-01' AND created_at < '2024-04-01';

Output

id | parent | notused | detail
---+--------+---------+----------------------------------------------------
3  | 0      | 213     | SCAN events USING COVERING INDEX idx_events_created

id | parent | notused | detail
---+--------+---------+--------------------------------------------------------------------------------------
3  | 0      | 153     | SEARCH events USING COVERING INDEX idx_events_created (created_at>? AND created_at<?)

Wrapped in strftime, every one of the 5 000 index entries has to be read and tested (SCAN). The bare-column range SEARCHes straight to March and reads only those entries.

Non-sargable	Sargable rewrite
`WHERE YEAR(order_date) = 2024`	`WHERE order_date >= '2024-01-01' AND order_date < '2025-01-01'`
`WHERE salary * 12 > 1200000`	`WHERE salary > 100000`
`WHERE lower(email) = 'x@y.in'`	Store normalised, or index `lower(email)`
`WHERE name LIKE '%son'`	Full-text / trigram index, or reverse-string column
`WHERE phone = 9876543210` (text column)	`WHERE phone = '9876543210'` — avoid implicit casts

2. Fetch less

Select only needed columns — enables covering indexes and reduces I/O and network.
Filter early in WHERE (not HAVING) and paginate with keyset pagination instead of big OFFSETs.
Use EXISTS for existence checks instead of COUNT(*) > 0 or JOIN … DISTINCT.
Use UNION ALL instead of UNION when duplicates are impossible.

3. Avoid row-by-row work

4. Help the planner

Keep statistics fresh: ANALYZE (PostgreSQL, SQLite), ANALYZE TABLE (MySQL), UPDATE STATISTICS (SQL Server).
Index foreign-key columns used in joins (order_items.product_id) — the PK side is indexed automatically, the FK side usually is not.
Match data types on join columns (INT = INT, not INT = VARCHAR).
Break monster queries into CTEs/temp tables when the planner mis-estimates.

A tuning workflow

Measure
Find the slow query (slow query log, pg_stat_statements, APM). Optimise what is actually slow and frequent.
Read the plan
Look for full scans on big tables, huge row estimates vs actuals, temp B-trees for sorts, nested loops over large inputs.
Fix the cause
Add/adjust an index, make predicates sargable, rewrite the query, reduce columns/rows.
Verify
Re-run EXPLAIN ANALYZE, and check write overhead of any new index.

Example: events of the user named user42. events (5 000 rows) is the “many” side of the join, and its user_id column has no index yet:

SQL

EXPLAIN QUERY PLAN
SELECT u.name, e.kind, e.created_at
FROM users u JOIN events e ON e.user_id = u.user_id
WHERE u.name = 'user42';

Output

id | parent | notused | detail
---+--------+---------+---------------------------------------------
3  | 0      | 216     | SCAN e
5  | 0      | 45      | SEARCH u USING INTEGER PRIMARY KEY (rowid=?)

The only way in is to scan all 5 000 events and look up each one's user. Index the foreign-key column and the plan flips: scan the small users table and seek into events.

SQL

CREATE INDEX idx_events_user ON events (user_id);

EXPLAIN QUERY PLAN
SELECT u.name, e.kind, e.created_at
FROM users u JOIN events e ON e.user_id = u.user_id
WHERE u.name = 'user42';

Output

id | parent | notused | detail
---+--------+---------+-------------------------------------------------
4  | 0      | 216     | SCAN u
8  | 0      | 61      | SEARCH e USING INDEX idx_events_user (user_id=?)

How would you optimise a slow SQL query?