Best Practices for Writing Fast and Clean SQL Queries

A fast query is not only about indexes. Most slow queries come from a few repeated habits, and fixing them costs nothing. Clean writing also matters, because someone (often you, months later) has to read the query again.

Common SQL Query Mistakes and How to Fix Them

Don’t Wrap Indexed Columns in Functions

This one catches many people. When you apply a function to a column inside WHERE, the database often can’t use the index on that column, because it would have to calculate the function for every row first.

Both queries below return the same orders from 2026. Only the second one can use an index on order_date.

-- Slower: the function hides the column from the index
SELECT order_id, amount
FROM orders
WHERE EXTRACT(YEAR FROM order_date) = 2026;

-- Faster: compare the column directly against a date range
SELECT order_id, amount
FROM orders
WHERE order_date >= '2026-01-01'
  AND order_date <  '2027-01-01';

Code Explanation:

• The first query calculates the year for every row, so it must read and process each row.
• The second compares the raw order_date value to two fixed dates, which lets an index on that column narrow the search.
• The upper limit uses < with the first day of the next year, which also covers any time of day on December 31.

Keep the Query Readable

• Write each major clause (SELECT, FROM, WHERE, GROUP BY) on its own line, and use the same capitalization style throughout.
• Use short but meaningful aliases, such as c for customers and o for orders, rather than a, b and c.
• Break long logic into CTEs with clear names, so each step can be read on its own.
• Add a short comment above anything that isn’t obvious, like a business rule or an odd filter.

Fast and clean usually go together. An easy-to-read query is also easier to debug.