Skip to the page
Tecyfy

SQL Query Optimization: 8 Tips That Work, and 3 That Don't

How to make a slow SQL query fast: read the plan, index what you filter on, keep indexed columns out of functions, use covering indexes and partitions. Measured on 2 million rows, with three popular tips that make no difference.

Tecyfy · Published · Updated · 9 min read

To make a SQL query faster, first read its execution plan to see where the time goes, then give the database a way to read less data: an index it can actually use, a filter it can push down, fewer columns, fewer rows. Many slow queries come down to a full table scan that an index or a small rewrite would avoid.

The examples below ran on SQLite with 2 million orders and 100,000 customers, so you can repeat them with nothing but Python. The plan output looks different in PostgreSQL, MySQL or SQL Server, but the reasons a query is slow are the same. We also tested three popular tips that turn out to make no difference.

1. Read the execution plan first

The plan is the database's description of how it will run your query: which tables it scans in full, which indexes it uses, how it joins. Every database can show one:

  • PostgreSQL: EXPLAIN for the plan, EXPLAIN ANALYZE to run the query and show the real time and row count of each step.
  • MySQL: EXPLAIN, and EXPLAIN ANALYZE from 8.0.18.
  • SQL Server: "Include Actual Execution Plan" in SQL Server Management Studio.
  • SQLite: EXPLAIN QUERY PLAN.

The word to look for is a scan of a large table where you expected a search. In SQLite it reads SCAN orders; in PostgreSQL, Seq Scan on orders. Every tip below is a way to turn a scan into a search, or to make the search touch fewer rows.

2. Index the columns you filter and join on

Finding one customer's orders without an index means reading all 2 million rows:

SELECT order_id, amount FROM orders WHERE customer_id = 4242;
-- no index:  SCAN orders                                 35.8 ms
CREATE INDEX idx_orders_customer ON orders (customer_id);
-- with it:   SEARCH orders USING INDEX idx_orders_customer  0.006 ms

That is thousands of times faster for the same 26 rows. Index the columns that appear in WHERE clauses and join conditions of frequent queries, foreign keys included. Not every database indexes a foreign key for you: PostgreSQL does not, and its documentation suggests adding one because deletes and joins on the referenced table need it.

Indexes are not free. Each one makes inserts and updates slower and takes space, so add them for the queries you actually run, not for every column.

3. Don't wrap an indexed column in a function

This is a common reason an index exists but is not used. Counting the orders of one day by taking the date of each timestamp:

SELECT COUNT(*) FROM orders WHERE date(created_at) = '2025-06-01';
-- SCAN ... (every row)                    75.9 ms
SELECT COUNT(*) FROM orders
WHERE created_at >= '2025-06-01' AND created_at < '2025-06-02';
-- SEARCH ... (created_at>? AND created_at<?)  0.038 ms

Both return 3,320. The first has to compute date() for every row before it can compare, so the index on created_at is no help. The second compares the column itself with a range, which the index can answer directly. The same applies to LOWER(email) = ..., YEAR(order_date) = 2025 or amount * 1.2 > 100. Rewrite the condition so the bare column is on one side, or, where your database supports it, create an index on the expression itself (PostgreSQL's indexes on expressions, for example CREATE INDEX ON users (lower(email))).

A LIKE with a leading wildcard has the same problem. name LIKE 'name42%' can use an index on name, because it is a range of the sorted values, but name LIKE '%42' cannot. In SQLite the prefix search only used the index once LIKE was made case-sensitive, a detail the SQLite documentation explains under "the LIKE optimization".

4. Use a covering index for hot queries

An index that contains every column a query needs lets the database answer from the index alone, without visiting the table (in PostgreSQL, only for rows on pages that have not changed recently) (in PostgreSQL, only for rows on pages that have not changed recently). For one month's revenue per customer:

SELECT customer_id, SUM(amount) FROM orders
WHERE created_at >= '2025-06-01' AND created_at < '2025-07-01'
GROUP BY customer_id;
-- index on (created_at) only, then fetch each row:   76.1 ms
CREATE INDEX idx_orders_created_cover ON orders (created_at, customer_id, amount);
-- USING COVERING INDEX:                              22.7 ms

The order of columns matters: the one you filter by range comes first. PostgreSQL can also add columns to an index only for covering, with INCLUDE (...).

5. Select only the columns you need

SELECT * reads and sends every column, including wide text you never look at, and it prevents a covering index from being enough. Name the columns. It also keeps the query working when someone adds a column to the table.

6. Filter before you join, and know that the optimizer helps

Joins cost the most when they have the most rows to match. Write the filters that make both sides smaller:

SELECT o.order_id, c.name
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE c.status = 'active' AND o.created_at >= '2026-08-01';

Here SQLite used the covering index to find the recent orders first, then looked up each customer by primary key. Modern optimizers push a WHERE condition down to the table it belongs to by themselves, so you rarely need a subquery or a temporary table just to filter first. What they cannot do is invent a filter you did not write, or use an index that does not exist.

Use INNER JOIN when you only want matching rows. An OUTER JOIN has to keep unmatched rows and gives the optimizer fewer choices of join order.

7. Partition very large tables by the column you filter on

Partitioning splits one large table into pieces by a column, usually a date. A query that filters on that column reads only the matching pieces, which the database calls partition pruning. In PostgreSQL:

CREATE TABLE orders (
  order_id   bigint,
  created_at date,
  amount     numeric
) PARTITION BY RANGE (created_at);

CREATE TABLE orders_2025 PARTITION OF orders
  FOR VALUES FROM ('2025-01-01') TO ('2026-01-01');
CREATE TABLE orders_2026 PARTITION OF orders
  FOR VALUES FROM ('2026-01-01') TO ('2027-01-01');

Partitioning pays off only when a table would otherwise be very large (a rule of thumb is a table bigger than the database server's memory) and makes dropping old data cheap (drop a partition instead of deleting rows). On a table of a few million rows, a good index usually does more. In data warehouses and lakehouses the same idea appears as partitioned or clustered tables, and the Databricks liquid clustering guide covers that case.

8. Return fewer rows, and cache in the application

LIMIT (or TOP in SQL Server, FETCH FIRST in standard SQL) stops a query that only needs the first page of results. Pair it with ORDER BY; without one, the database may return any rows it likes. For deep pages, OFFSET 100000 still reads and throws away 100,000 rows, so page with a condition on the last key seen (WHERE order_id > :last_id ORDER BY order_id LIMIT 50).

For results that many users ask for and that change slowly, such as a dashboard's monthly totals, cache them in the application or a cache such as Redis, or store them in a summary table refreshed on a schedule. Do not count on the database to cache results for you: MySQL's query cache, once a common tip, was removed in MySQL 8.0.

Three tips that make no difference

We measured three pieces of advice that are often repeated:

AdviceWhat we measured
COUNT(order_id) is faster than COUNT(*)51.4 ms against 49.3 ms, the same plan. COUNT(*) counts rows; COUNT(column) counts non-null values of that column, so use it only when you mean that.
IN (...) is faster than OR11.6 ms against 11.3 ms, the same index search. SQLite rewrites an OR of equalities on the same column into IN. Use whichever reads better.
Subqueries or temporary tables to "filter before joining"The optimizer already pushed the filters down. A temporary table helps when you reuse an intermediate result several times, not as a general speed-up.

These differ between databases and versions, which is the real lesson: check the plan and time the query on your own data rather than trusting a rule.

Tecyfy Takeaway

Read the plan, find the full scan, and give the database a way to avoid it: an index on the filter and join columns, conditions that leave the indexed column bare, a covering index for the queries that run all day, and only the columns and rows you need. Partition when a table grows bigger than the database server's memory. Then measure, because the advice that sounds right is not always what your database does.

Questions

How do I make a slow SQL query faster?
Read its execution plan to see where the time goes, then give the database a way to read less data: an index it can use, a filter it can push down, fewer columns and fewer rows. Most slow queries are a full table scan that an index or a small rewrite avoids.
Why is my SQL index not being used?
Often because the indexed column is wrapped in a function, as in date(created_at) = '2025-06-01' or LOWER(email) = '...'. Rewrite the condition so the bare column sits on one side, such as a range on created_at. A LIKE with a leading wildcard cannot use the index either.
What is a covering index?
An index that contains every column a query needs, so the database can answer from the index alone (in PostgreSQL it still visits the table for rows on recently changed pages). Put the column you filter by range first; PostgreSQL can add extra columns with INCLUDE.
Is COUNT(*) slower than COUNT(column)?
No. In our test the 2 took the same time with the same plan. They mean different things: COUNT(*) counts rows, COUNT(column) counts non-null values of that column.
When should I partition a table?
When the table is very large, as a rule of thumb bigger than the database server's memory, and queries filter on one column, usually a date, so the database reads only the matching partitions. On a table of a few million rows a good index usually does more.