SQL Tuning: Making Database Queries Faster by Reading Their Plans
Learn to read a query plan, add indexes that match your filters, and rewrite slow queries so they return the same data using less time and fewer resources.
In short
SQL tuning makes database queries faster by reading their query plans, adding indexes that match the filters, and rewriting slow queries to return the same data.
10 min readFree
What You Will Learn
SQL tuning means making a database query return the right data while using less time and fewer resources. A query is a question you send to a database, such as 'show me this customer's recent orders.' Tuning changes how the database answers that question. You can change the query text, or you can change the indexes the database uses to find rows. In system design, this skill keeps a system fast as the amount of data and the number of users grow.
By the end of this lesson you will be able to open a query plan, spot a full table scan, design an index for a query, and rewrite three common slow patterns. You will also learn why the N+1 pattern makes a page slow even when each single query looks small.
Key words
- Query
- A request written in SQL that asks the database to read, add, change or remove data.
- Table
- A grid that stores one kind of data, such as all orders. Each column is a field, such as total.
- Row
- One record in a table, such as one order.
- WHERE clause
- The part of a query that says which rows to keep.
- Index
- A sorted helper structure the database keeps for one or more columns, so it can find matching rows without checking every row.
- Query plan
- The method the database picks to run a query, step by step.
- EXPLAIN
- A word you put in front of a query to ask the database to show its plan.
- Table scan
- Reading every row in a table to see which ones match.
- N+1 pattern
- Running one query for a list, then one more query for each item in that list.
How Do You Read a Query Plan?
Before you change anything, ask the database how it plans to run the query. In PostgreSQL and MySQL you place EXPLAIN in front of the query. SQLite uses a similar command called EXPLAIN QUERY PLAN, which prints a short summary of which tables it reads, in what order, and which indexes it uses. The names change from one database to another, but the ideas stay the same.
EXPLAIN ANALYZE goes one step further. It really runs the query and reports real timings. Be careful with it on UPDATE or DELETE statements, because those will change your data when they run.
Most plans are read in the same way. Look at the access method for each table first. Then look at the cost and the row counts. The next parts of this section cover each one.
What Is the Difference Between a Scan and a Seek?
Scan or seek is the first question to answer. The plan tells you how the database reads each table. Three names come up often. Exact words differ between databases, but the idea is the same in each one.
Full table scan
The database reads every row in the table and checks each one against the WHERE clause. On a small table this is fine. On a table with millions of rows it is one possible cause of a slow page.
Index scan
The database walks through a whole index, or a large part of it, in order. It can be better than a full table scan, but it still touches many entries.
Index seek
The database jumps straight to the rows that match, using the index like a book's index. This is what you want for a filter that picks a small share of the rows.
How Do You Check What Each Step Costs?
A plan is a list of steps. Each step has a cost and an estimated number of rows. The cost is the database's guess at how much work the step needs. It is a planning number, not a clock time, so compare steps inside the same plan rather than across different machines.
- 1
Find the biggest table
Look for the table with the most rows.
- 2
Compare estimated and real rows
If you used EXPLAIN ANALYZE, check whether the real row count is far from the estimate. A big gap often means the database's statistics are out of date.
- 3
Look for sorts
A step that sorts many rows can be slow. An index that already returns rows in order can remove that sort.
- 4
Find the most expensive step
Find the step with the highest cost or the most time. Fix that one first. Tuning a cheap step does not help much.
Tip
Change one thing at a time. Run EXPLAIN again after each change, and keep the result only if the plan and the timing both get better.
How Do You Design Indexes That Match Your Query?
An index helps only when the database can use it to find rows. The best index matches the columns your query uses to filter and sort. Look at three places in the query before you design one.
WHERE columns
Columns you filter on, such as user_id or status. These are the most common reason to add an index.
JOIN columns
Columns used to connect two tables, such as orders.user_id matched with users.id. Indexes on the join columns let the database find matching rows in the other table quickly.
ORDER BY columns
Columns used to sort the result, such as created_at. An index in the same order can return the newest rows without a separate sort step.
When a query filters on one column and sorts on another, use one index with both columns. Put the column you compare with equals first, then the column you sort on. For example, an index on (user_id, created_at) helps a query that asks for one user's orders, newest first. An index on (created_at, user_id) would not help that same query as well, because the database cannot jump to one user's rows first.
What Is the Cost of an Index?
An index is not free. Every index makes some reads faster, but it also adds work and space. Think of it as a deal with three sides.
Faster reads
Queries that match the index can find rows without scanning the table. This is the reason to add it.
Slower writes
When you insert, update or delete a row, the database must also update every index that includes the changed columns. A table with many indexes gets slower to write.
More storage
Each index is stored on disk, and it can take a large share of space on a big table.
Tip
Do not add an index for every column. Add one for a query that is slow and used often, then check the plan again. Remove indexes that no query uses.
Rewriting Slow Queries
Sometimes the index is fine and the query text is the problem. Rewriting a query means changing the words you send, not the meaning of the result. These four changes fix many slow pages.
Select only needed columns
Ask for the fields the screen shows, not every column with SELECT *. Fewer columns means less data read and sent back, and it may let the database answer from an index alone.
Filter early
Put the conditions in WHERE so that the database removes unneeded rows before it joins or sorts them. Do not fetch a large set and filter it later in the application.
Avoid functions on indexed columns
When you wrap an indexed column in a function, the index often cannot be used. Compare the raw column with a range instead.
Use pagination
Pagination means returning one page of results at a time, using LIMIT. Never load every order to show the first twenty.
-- Slow: the function hides created_at from the index
SELECT id, total FROM orders
WHERE DATE(created_at) = '2026-03-01';
-- Better: compare the raw column with a range
SELECT id, total FROM orders
WHERE created_at >= '2026-03-01'
AND created_at < '2026-03-02';Fixing the N+1 Pattern
The N+1 pattern is one of the most common causes of slow pages. The code runs one query to get a list, such as 50 orders. Then it runs one more query for each order, such as the items in that order. That is 51 queries for one screen. Each query may be quick, but the total time adds up, and the time grows with the list size.
- 1
Spot the loop
Look for a database call inside a loop. If the number of calls rises with the number of rows shown, you have an N+1 pattern.
- 2
Fetch the related rows in one query
Use a JOIN, or one query with IN (...) that lists all the order ids. Then group the results in the application.
- 3
Count the queries again
Check that the page now runs a fixed number of queries, whatever the list size.
Worked Example: Order History
An online shop has an order-history screen. It shows one customer's recent orders. The screen takes 4 seconds. The orders table has millions of rows, and the query filters by user_id. Here is how to fix it, one step at a time.
- 1
Read the slow query
The query asks for one user's orders, newest first, but it has no LIMIT. It returns every matching order to the page, even though the page shows only the latest 20.
- 2
Ask for the plan
EXPLAIN shows a full table scan on orders. The database reads millions of rows to find this user's rows, then sorts them.
- 3
Add a matching index
Create an index on (user_id, created_at). The first column matches the filter, and the second matches the sort.
- 4
Add LIMIT
Ask for only the first page of results. The database can stop after 20 rows instead of building the full list.
- 5
Check the plan again
The plan now shows an index seek on orders, with no full scan and no separate sort. The screen loads in milliseconds.
CREATE INDEX idx_orders_user_created
ON orders (user_id, created_at);
SELECT id, total, created_at
FROM orders
WHERE user_id = 42
ORDER BY created_at DESC
LIMIT 20;The index cost some write speed and some storage, as the trade-off section explained. For an order-history screen that is read far more often than orders are placed, that is a fair deal.
Check Yourself
Quick check
A query runs WHERE status = 'paid' ORDER BY created_at LIMIT 20 on a table with 5 million rows. The plan shows a full table scan and then a sort. Which index fits this query best?
Quick check
A developer writes WHERE DATE(created_at) = '2026-03-01' and the query is slow, even though created_at has an index. What is the best fix?
Summary
Start with the query plan, not with guesses. Find the step that reads the most rows, and check whether it is a full table scan. Design an index that matches the WHERE, JOIN and ORDER BY columns, and remember that every index slows writes and uses storage. Rewrite the query to select only needed columns, filter early, keep functions off indexed columns, and page through results. Finally, look for the N+1 pattern, because a page that runs one query per row will slow down as the data grows.
Sources
- Stack Practices: Complete Guide to SQL Query Optimization
- How to Read SQL Execution Plans: A Beginner's Guide (hisqlboy)
- DEV Community: A practical SQL query tuning playbook
- Runnable SQLite Docs: EXPLAIN QUERY PLAN (coddy.tech)
- MySQL EXPLAIN Guide: Read Query Plans and Optimize Slow SQL (digibeatrix)
- Oracle EXPLAIN PLAN: Read, Compare, and Tune SQL (cleverence.com)
- GeeksforGeeks: SQL Query Optimizations
- Readyset: What Is the N+1 Query Problem? How to Detect and Fix It
- SQLint: SQL Query Optimization: 10 Techniques for Faster Queries
- Tutorial Reference: SQL Query Optimization Best Practices
- Data Engineer Things: SQL Query Optimization: Modern Techniques and Best Practices
- ecn-apps: SQL Query Optimization Guide 2026
- OneUptime: How to Fix Slow Database Queries Issues
- Mastering SQL Query Performance Tuning (Medium, itversity)
Last updated on 17 November 2026
Related lessons
Learn with me
Every lesson is free. I add new ones as I learn. Have a question, or want help with your own work? Book a short call.