Query Optimization

Explanation

Query optimization is the art of helping the database's query planner do less work. The planner already chooses how to run your SQL, but the way you write the query strongly influences its choices.

Two golden rules:

1. Read less data. Filter early, select only needed columns, and let indexes do the seeking.
2. Avoid surprises. Correlated subqueries, functions on indexed columns, and accidental Cartesian products all blow up row counts.

Use EXPLAIN to see the plan the database intends to use — it reveals full table scans, join strategies, and whether an index is used.

Syntax

EXPLAIN
SELECT col FROM table WHERE indexed_col = 1;

Interactive Example

Compare a targeted query against a wider one with EXPLAIN. Notice how filtering and selecting fewer columns changes the described plan.

Common Mistakes

• Filtering after a join with no index. Moving WHERE conditions earlier shrinks the rows fed into the join.
• Functions on indexed columns. WHERE LOWER(email) = ... ignores a plain index; normalize data instead of transforming it in the predicate.
• SELECT * in apps. It forces the engine to read and ship every column.

Best Practices

• Filter in WHERE before joining; smaller inputs make faster joins.
• Select only the columns you use so the planner can consider index-only scans.
• Prefer EXISTS over IN (subquery) for large sets, and JOINs over correlated subqueries when possible.
• Check the EXPLAIN plan after each change to confirm it helped.

Practice Question

Rewrite a query that uses WHERE YEAR(order_date) = 2022 on orders so it can use a plain index on order_date (hint: a range comparison), and run EXPLAIN on both versions.

Summary

Optimize by reducing the data the planner must touch: filter early, select specific columns, use indexes, and avoid functions on indexed columns. Always confirm changes with EXPLAIN rather than intuition.

FAQ