SQL Query Optimization: Read Execution Plans and Fix Slow Queries, With Timings

A query that took milliseconds last year can take minutes today, simply because the table grew. SQL query optimization is less about clever tricks and more about a repeatable process: find out what the database is doing, remove the wasted work, and check that it helped.

We tested each technique below on one million generated rows in SQLite 3.50. Timings are the best of several runs from one laptop, using an in-memory database. They will not match your system, and PostgreSQL, MySQL and SQL Server each have their own optimizers and plan formats. Focus on the size of each improvement and on the principles behind it.

Results at a glance

The test setup

CREATE TABLE customers (
  id INTEGER PRIMARY KEY, name TEXT, city TEXT
);
CREATE TABLE orders (
  id INTEGER PRIMARY KEY, customer_id INT, status TEXT,
  created TEXT, total INT, note TEXT
);
-- 50,000 customers and 1,000,000 orders with random values

A simple optimization process

1. Find the queries that matter. Use slow query logs or monitoring. A 200 ms query running a thousand times a minute may matter more than a 10-second monthly report.
2. Read the plan. See what the database is really doing.
3. Change one thing. Add an index or rewrite part of the query.
4. Measure again, on realistic data. A plan on 100 test rows tells you very little about a million.
5. Watch it over time. Plans can change as data grows and statistics shift.

Read an execution plan

An execution plan is the database’s step-by-step strategy for running a query, including which indexes it uses and in what order it joins tables. Prefix a query to see it:

Note that EXPLAIN ANALYZE in PostgreSQL really runs the query, so be careful when using it with UPDATE or DELETE. Here is what to look for first:

A full scan is not automatically bad. If a query needs most of a small table, scanning can be the fastest choice. In PostgreSQL’s EXPLAIN ANALYZE, also compare estimated rows with actual rows. A big difference often means table statistics are out of date.

Add the right index

This query finds one customer’s orders:

SELECT id, total FROM orders WHERE customer_id = 123;

Before adding an index, the plan was a full scan:

SCAN orders
-- 20 rows in 73 ms

Then we added an index on the filter column:

CREATE INDEX idx_orders_customer ON orders(customer_id);

SEARCH orders USING INDEX idx_orders_customer (customer_id=?)
-- 20 rows in 0.01 ms

The same 20 rows came back thousands of times faster, because the database no longer read a million rows to find them. No other technique in this article gave us a larger win. Good first candidates are columns used in WHERE, JOIN and ORDER BY, especially when they narrow the results down to few rows. Our guide to SQL indexes explains how they work.

Do not wrap indexed columns in functions

Here are two ways to find March orders, with an index on created:

-- Function on the column
SELECT id FROM orders WHERE substr(created, 1, 7) = '2026-03';
-- SCAN orders USING COVERING INDEX idx_orders_created
-- 84,961 rows in 96 ms

-- Plain range
SELECT id FROM orders
WHERE created >= '2026-03-01' AND created < '2026-04-01';
-- SEARCH orders USING COVERING INDEX idx_orders_created (created>? AND created<?)
-- 84,961 rows in 33 ms

Both return identical rows, yet the first took about three times as long. The plan shows why: with a function on the column, the database cannot jump to a position in the sorted index, so it has to SCAN through every entry. The range version can SEARCH straight to the right place. A condition that can use an index in this way is called sargable.

The same problem appears with these common patterns:

We checked that last one too. A leading wildcard produced SCAN customers, as an index sorted from the first letter cannot help with a match at the end.

Composite indexes

When a query filters on two columns, a single-column index does only part of the job:

SELECT id FROM orders
WHERE status = 'paid'
  AND created >= '2026-03-01' AND created < '2026-03-08';

-- Using only the created index: 6,198 rows in 19 ms

CREATE INDEX idx_status_created ON orders(status, created);

-- SEARCH orders USING COVERING INDEX idx_status_created
--   (status=? AND created>? AND created<?)
-- 6,198 rows in 2.2 ms

Column order matters a great deal. An index on (status, created) works like a phone book sorted by surname and then first name. You can look up a surname alone, but not a first name alone. Our query on status alone could use this index. Filtering on created alone generally could not use it efficiently. As a rule of thumb, put columns tested with equality first and range columns after them.

That plan also says covering index, meaning the index held everything the query needed, so the table itself did not have to be read. Selecting only the columns you need makes this more likely, which is another reason to avoid SELECT *.

Faster pagination

Using OFFSET for deep pages is a hidden cost, since the database still has to walk past all the skipped rows:

-- Page deep into the results
SELECT id FROM orders ORDER BY id LIMIT 20 OFFSET 500000;
-- 8.1 ms

-- Keyset pagination: continue from the last id seen
SELECT id FROM orders WHERE id > 500000 ORDER BY id LIMIT 20;
-- 0.01 ms

Keyset pagination stays fast no matter how deep you go. It also avoids rows shifting between pages when new data is added. The trade-off is that users cannot jump straight to page 5,000, which is rarely a real need. On tables with wide rows, or with more rows to skip, the gap can be much larger than it was in our test.

Write leaner queries

• Select only the columns you need. SELECT * sends more data, and it can prevent an index from covering the query.
• Filter early. Use WHERE to remove rows before grouping, and HAVING only for conditions on aggregates. See SQL GROUP BY and HAVING.
• Use LIMIT when you need only a few rows.
• Prefer UNION ALL when you do not need duplicates removed. Our UNION vs UNION ALL test shows what that saves.
• Avoid unnecessary DISTINCT. It is often hiding a join that produces duplicates.
• Use EXISTS for existence checks. It can stop at the first match, as our guide to SQL subqueries explains.
• Watch for N+1 queries. One query per row in a loop, usually caused by application code, is often the real problem. A single join or batched query can replace hundreds of round trips.

Joins

Index the columns you join on. We ran a total-spend report for customers in one city, and the plan was exactly what you would hope for:

SELECT c.name, SUM(o.total)
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE c.city = 'Goa'
GROUP BY c.id;

-- SCAN c
-- SEARCH o USING COVERING INDEX idx_cover (customer_id=?)
-- 9,900 rows in 34 ms

It scans the customers, then uses an index to find each one’s orders. Without an index on orders.customer_id, the database would have to do much more work for each customer. An index on the foreign key is one of the most common and valuable additions you can make. To refresh your understanding of the basics, see SQL joins explained.

When indexes cost you

Indexes are not free, so do not add one for every column:

• Slower writes. Every insert, update and delete must also update each index.
• More storage and memory. Indexes can grow large on big tables.
• Little benefit on unselective columns. An index on a true or false column may not help if most rows match.
• Redundancy. An index on (a) is often unnecessary if you already have one on (a, b).

Check usage statistics periodically, and remove indexes nothing uses. And keep statistics fresh with ANALYZE or your database’s equivalent, because the optimizer relies on them to choose a good plan. Sometimes the slow part is contention rather than the query. Long-running transactions holding locks can make a fast query seem slow.

Common mistakes

• Optimizing without measuring. Many confident guesses turn out to be wrong.
• Testing with tiny data. Plans and timings change dramatically as tables grow.
• Trusting a tip for another database. Advice for one engine may not apply to another.
• Adding indexes blindly. Each one slows down writes.
• Ignoring the first-run effect. Caches make a second run faster, so run several times.

Final thoughts

Most slow queries have ordinary causes: a missing index, a function on a column, or too much data being fetched. Take your slowest real query this week, run EXPLAIN on it, and look for a full scan on a large table. Fix one thing, measure again, and keep notes on what you changed.

Frequently asked questions

Keep learning