Query Optimisation

Query tuning attracts folklore. "Always use EXISTS instead of IN." "Never use SELECT *." "UNION is slow." Some of these are sound, some were true on a database version from fifteen years ago, and some were never true.

The way to tell them apart is to measure. Every rewrite in this lesson should be verified on your data, because the right answer genuinely depends on the data distribution, the indexes and the server version.

-- 1. Make the filter searchable: let the index be used
-- Before
WHERE YEAR(o.order_date) = 2026 AND UPPER(c.country) = 'INDIA'
-- After (with a case-insensitive collation, the UPPER is unnecessary)
WHERE o.order_date >= '2026-01-01' AND o.order_date < '2027-01-01'
  AND c.country = 'India'

-- 2. Filter before joining, not after
-- Before: joins every order, then discards most of the result
FROM customers AS c
JOIN orders AS o ON o.customer_id = c.customer_id
WHERE o.order_date >= '2026-01-01'
-- After: only 2026 orders ever enter the join
FROM customers AS c
JOIN (
    SELECT order_id, customer_id, shipping_fee
    FROM orders
    WHERE order_date >= '2026-01-01'
) AS o ON o.customer_id = c.customer_id

-- 3. Ask an existence question instead of counting
-- Before: counts every matching row, then compares
WHERE (SELECT COUNT(*) FROM orders AS o WHERE o.customer_id = c.customer_id) > 0
-- After: stops at the first match
WHERE EXISTS (SELECT 1 FROM orders AS o WHERE o.customer_id = c.customer_id)

-- 4. Return the columns you use, not every column
-- Before
SELECT * FROM orders WHERE customer_id = 417
-- After: can be answered from a covering index alone
SELECT order_id, order_date, status FROM orders WHERE customer_id = 417

• Rewrite 1 has the clearest mechanism: removing the function on the column allows an index seek. This is the single most productive rewrite in most codebases.
• Rewrite 2 is worth an honest caveat. A good optimiser often pushes the WHERE down on its own, so the two forms may produce identical plans. Where it helps is when the filter cannot be pushed down — across an outer join, or before an aggregation. Check the plan rather than assuming.
• Rewrite 3 is sound because EXISTS can stop at the first matching row while COUNT(*) must find them all. On a customer with 4,000 orders that is 3,999 rows of unnecessary work.
• Rewrite 4 matters for two reasons: fewer bytes over the network, and the chance that a narrow index covers the query so the table is never read.

Before tuning a query, check that you are tuning the right thing. Two patterns cost more than any amount of clever SQL, and both are invisible in a single query's plan.

The first is querying in a loop. An application fetches 500 orders, then fetches each order's customer individually. That is 501 round trips, and each one pays network latency. Every individual query looks fast in the plan. The page takes eight seconds. The fix is one query with a join, and with an ORM it usually means loading the related data explicitly rather than lazily.

The second is fetching data you do not use. Returning 50,000 rows to count them in application code, or selecting 40 columns to display 4. The database can count and it can project — asking it to is usually a single-line change with a large effect.

One more effect worth knowing by name, because it produces a genuinely confusing symptom. A database caches the plan for a parameterised query, built for the parameter values it saw first. If those values were unusual — a customer with three orders, when most have thousands — the cached plan can be badly suited to later calls.

This is parameter sniffing. The symptom is a query that is fast for most inputs and slow for some, or fast for weeks and then slow after a restart, with no code change. Diagnosing it is beyond this lesson; recognising the pattern stops you looking for a bug in the SQL that is not there.