How to Optimize SQL Queries: 20 Proven Best Practices

A slow SQL query is one of the easiest ways to ruin a fast application.

You can have a clean architecture, a great cache, and a powerful server - and still watch a page hang because one query scans a million rows without an index.

Over the years, I have optimized hundreds of SQL queries across many production systems.

Most of the wins come from the same small set of techniques, applied again and again.

Some of them are obvious.
Some of them go against advice you have probably heard.

To make them easy to remember, I have grouped 20 proven techniques into 6 themes you can reach for whenever a query gets slow.

In this post, we will explore:

• Write index-friendly (sargable) queries
  • Use indexes wisely
  • Avoid functions on columns in WHERE
  • Avoid leading wildcards in LIKE
  • Match data types exactly
• Fetch only the data you need
  • Stop using SELECT *
  • Filter early
  • Paginate with Keyset instead of OFFSET
  • Query only what changed
• Simplify joins and subqueries
  • Reduce JOIN complexity
  • Choose the right JOIN type
  • Replace redundant subqueries with JOINs or CTEs
  • Prefer EXISTS over IN
• Design your schema for reads
  • Normalize wisely
  • Use materialized views
• Make writes and transactions efficient
  • Batch large operations
  • Keep transactions short
• Let the database help you
  • Read the execution plan
  • Keep statistics up to date
  • Use query hints sparingly
  • Monitor and tune continuously

Let's dive in.

All queries in this post were tested on the PostgreSQL database. The same ideas apply to other databases, though the exact syntax may differ.

---

👉 Read original article on my newsletter: https://antondevtips.com/blog/how-to-optimize-sql-queries-20-proven-best-practices

Write Index-Friendly (Sargable) Queries

An index only helps if your query lets the database use it.

A "sargable" query (from Search ARGument ABLE) is one where the database can jump straight to the rows it needs through an index, instead of scanning the whole table.
The four techniques below keep your queries sargable.

1. Use Indexes Wisely

Indexes are the single biggest lever for read performance.

An index is a sorted data structure that lets the database find rows without scanning the entire table, much like the index at the back of a book saves you from reading every page.

Create indexes on the columns you filter, join, sort, and group by most often - the columns in your WHERE, JOIN, ORDER BY, and GROUP BY clauses.

When several columns are used together, a single composite index that covers them is far better than separate single-column indexes:

-- A composite index for frequent filters on status and order date
CREATE INDEX idx_orders_status_order_date
    ON orders (status, order_date);

This index speeds up queries that filter on status, and on status together with order_date.

Column order matters: an index on (status, order_date) helps queries that filter by status first, but not queries that filter by order_date alone.

You can also create a covering index that stores extra column values inside the index.
This helps when a query only needs columns available in the index, so the database can avoid reading the actual table rows.

It is especially useful for small, frequent lookup queries.

-- Cover a common read query without fetching rows from the table
CREATE INDEX idx_orders_status_order_date_covering
    ON orders (status, order_date)
    INCLUDE (customer_id, total_amount);

SELECT customer_id, total_amount
FROM orders
WHERE status = 'paid'
  AND order_date >= DATE '2026-01-01';

Note: indexes are not free. Every index must be updated on each insert, update, and delete, and it takes disk space. Index the columns your queries actually use, not every column.

For a deeper look at index types and trade-offs, see Optimizing SQL Performance with Indexing Strategies.

2. Avoid Functions on Columns in WHERE

Wrapping a column in a function is one of the most common ways to accidentally disable an index.

When you call a function on a column, the database has to compute that function for every row before it can compare it, so it cannot use the index on the raw column:

-- Bad: the function on order_date prevents an index scan
SELECT * FROM orders
WHERE EXTRACT(YEAR FROM order_date) = 2025;

Rewrite the condition to compare the raw column against a range. This keeps the query sargable and lets the index do its job:

-- Good: a sargable range over the raw column
SELECT * FROM orders
WHERE order_date >= '2025-01-01'
  AND order_date <  '2026-01-01';

Both queries return the same rows, but only the second can use an index on order_date.

The same rule applies to LOWER(email), CAST(...), and arithmetic on a column.
If you often need to filter on a computed value, create an expression index for that exact expression instead.

3. Avoid Leading Wildcards in LIKE

A LIKE pattern that starts with a wildcard cannot use a normal index.

The database reads an index from left to right, so it needs to know the beginning of the value.
A pattern like '%son' hides the beginning and forces a full scan:

-- Bad: leading wildcard forces a full table scan
SELECT * FROM customers
WHERE last_name LIKE '%son';

-- Good: a known prefix allows an index range scan
SELECT * FROM customers
WHERE last_name LIKE 'Anders%';

If you genuinely need to search inside text - a "contains" search - a normal B-tree index will not help.

Reach for full-text search or a trigram index (the pg_trgm extension in PostgreSQL) built for that job.

4. Match Data Types Exactly

Comparing two different data types forces the database to convert one of them, and that conversion can silently disable an index.

If a column is an integer but you compare it to a string, or you join an integer key to a bigint key, the database adds an implicit cast - and the index on the original column may be skipped.

Keep the types identical on both sides of every join and filter:

CREATE TABLE logs (
    log_id     int PRIMARY KEY,
    event_date timestamptz NOT NULL,
    user_id    int NOT NULL
);

Define logs.user_id to match the type of users.id so the join uses the index on both sides.

Matching types is a small detail at design time that saves you from a confusing, hard-to-spot scan later.

Fetch Only the Data You Need

The fastest data to process is the data you never read.

Every column and every row you pull costs disk I/O, memory, and network time. These four techniques shrink the result set as early and as much as possible.

5. Stop Using SELECT *

SELECT * pulls every column, including the ones you do not need.

That means more data to read from disk, more to send over the network, and more memory to hold - all for columns your code throws away.

It also blocks covering indexes, where the index alone can answer the query without touching the table:

-- Bad: fetches every column
SELECT * FROM customers;

-- Good: only the columns you use
SELECT customer_id, first_name, last_name
FROM customers;

Listing columns explicitly is also safer. Your query will not silently change shape or break when someone adds or reorders a column.

6. Filter Early

The smaller the dataset you work with, the faster everything downstream becomes.

You may have heard the common advice:

Apply your most selective filters to cut the number of rows before joins and aggregations have to process them:

SELECT o.order_id, o.total
FROM orders o
WHERE o.order_date >= '2025-01-01'
  AND o.order_date <  '2025-02-01';

Note: in most cases you do not have to place these filters by hand.
A modern cost-based planner already pushes predicates down - it applies your WHERE conditions as early as it can and reorders joins on its own.
Rewriting the textual order of a WHERE clause rarely changes the plan.

What actually helps is giving the planner a selective, sargable filter and an index to apply it with.
So focus on making the filter index-friendly, not on where you type it.

7. Paginate with Keyset instead of OFFSET - When you can

OFFSET looks like an easy way to implement pagination, but it gets slower the deeper you go.

To return page 1,000, the database still reads and throws away every row before it.
OFFSET 100000 means reading 100,000 rows just to skip them.

Keyset pagination (also called seek pagination) remembers the last value you saw and seeks straight past it:

-- Key-based pagination on an indexed column
SELECT *
FROM orders
WHERE order_id > 1000
ORDER BY order_id
LIMIT 10;

Each page costs the same, whether it is page 2 or page 2,000, because the index jumps directly to order_id > 1000.

The trade-off: keyset pagination gives you fast next- and previous-page navigation, but you can't implement random jumps to any page number.

8. Query Only What Changed

Re-reading an entire table on every run is wasteful when only a few rows have changed.

Track a watermark - the last point you processed - and fetch only the rows newer than it:

-- Fetch only records updated since the last run
SELECT *
FROM records
WHERE modified_date > '2025-05-20 00:00';

This incremental pattern turns a full-table scan into a small, indexed range read.

Index modified_date, store the new high-water mark after each run, and your sync job stays fast even as the table grows.

---

👉 Read original article on my newsletter: https://antondevtips.com/blog/how-to-optimize-sql-queries-20-proven-best-practices

Comments

[Umer Farooq]

Nice one Anton. Point 4 reminded me of a classic EF Core + SQL Server trap. EF sends strings as nvarchar by default, so if the column is varchar, SQL Server converts the column and your index seek becomes a scan. IsUnicode(false) on those properties fixes it.

Any plans for a SQL Server version? Parameter sniffing deserves its own post.