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

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. Most 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 the most 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. 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 and SQL Server 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 helps tables of hundreds of millions of rows 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:

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 tables reach hundreds of millions of rows. Then measure, because the advice that sounds right is not always what your database does.