Skip to content
Tutorials Center
My learning

Topics

Lesson 88 of 125 ยท Performance

Writing Fast Queries

Write fast SQL queries. Keep conditions sargable, select only what you need, choose between EXISTS, IN and JOIN, fix N+1 queries and measure every change.

Most slow queries aren't slow because the database is weak. They're slow because they ask the database to do far more work than necessary: read every row when ten would do, run a subquery thousands of times, or ship a million rows to the application only to count them there. The good news is that a handful of habits fix the large majority of these cases.

This lesson collects those habits. You'll learn to write sargable conditions that can use indexes, to avoid hiding indexed columns inside functions, to fetch only the columns and rows you need, to choose between EXISTS, IN and JOIN, to spot and fix the infamous N+1 query problem, and, most importantly, to measure instead of guessing.

You'll use the plan-reading skills from Reading Query Plans throughout, together with a simple trick for timing queries right here in the browser.

Measure first

Optimization without measurement is superstition. Two tools work together:

  • The query plan tells you what the database does (scan or search, sort or not).
  • A timer tells you how long it takes, on realistic data.

SQLite's unixepoch('subsec') returns the current time with millisecond precision, so you can record timestamps in a small table between statements and subtract them. The example below builds 100,000 orders, then times two ways of asking for March 2024:

SQL Timing two versions of a query

Press Run (or Ctrl + Enter) to see the result here.

Both versions find the same 4,247 orders. Your exact timings depend on your device, but version A typically takes many times longer than version B. The plans explain why: A is a SCAN orders that calls strftime 100,000 times, while B is a SEARCH ... USING INDEX idx_orders_date (ordered_on>? AND ordered_on<?) that touches only the March rows.

Timing tips. Run each version a few times and compare typical values, because the first run of anything can be slower. Measure on realistic amounts of data: with 100 rows everything is instant and every version looks equally good. On a server database, also look at the database's own numbers (EXPLAIN ANALYZE in PostgreSQL and MySQL, SET STATISTICS TIME ON in SQL Server) rather than only the time your application sees.

Keep conditions sargable

A condition is sargable (from "Search ARGument ABLE") when the database can answer it by searching an index. The rule of thumb: leave the indexed column alone on one side of the comparison, and put all the calculation on the other side. An index on ordered_on stores the values of ordered_on, not of strftime('%Y-%m', ordered_on), so the moment you wrap the column in a function, the index can't help.

Not sargable Sargable rewrite
strftime('%Y', ordered_on) = '2024' ordered_on >= '2024-01-01' AND ordered_on < '2025-01-01'
date(created_at) = '2024-03-15' created_at >= '2024-03-15' AND created_at < '2024-03-16'
total * 1.2 > 590 total > 590 / 1.2
total + 0 > 490 total > 490
lower(email) = '[email protected]' Store emails normalized, or add an index on lower(email)
substr(sku, 1, 3) = 'KIT' sku >= 'KIT' AND sku < 'KIU', or sku GLOB 'KIT*'
name LIKE '%son' No index can help a leading wildcard; consider full-text search

Check a few of these against the plans:

SQL Sargable and non-sargable conditions

Press Run (or Ctrl + Enter) to see the result here.

The arithmetic rewrite turns SCAN orders into SEARCH orders USING COVERING INDEX idx_orders_total (total>?) (covering, because the query only needs id and total), and GLOB 'customer7*' becomes a range search on the email index (email>? AND email<?). The lower(email) and leading-wildcard queries stay full scans.

Why didn't we use LIKE 'customer7%' for the prefix search? In SQLite, LIKE is case-insensitive for ASCII letters, while an ordinary index is sorted case-sensitively, so SQLite can only use an index for a LIKE prefix when the index was created with COLLATE NOCASE (or after PRAGMA case_sensitive_like = ON). PostgreSQL has a similar catch: a LIKE 'abc%' prefix search can use a B-tree index only with the C collation or a text_pattern_ops index. When a function is unavoidable, all major databases let you index the expression itself. That's the subject of Partial, Expression and Covering Indexes.

Watch for hidden conversions. In MySQL and SQL Server, comparing a text column with a number (WHERE phone = 5550100 on a VARCHAR column) converts every row's value and silently disables the index. Compare values of the same type: WHERE phone = '5550100'. SQLite's type affinity handles this particular case, but writing matching types is a good habit everywhere.

OR conditions

OR on the same column is easy: SQLite treats status = 'pending' OR status = 'cancelled' like status IN ('pending', 'cancelled') and searches the index for each value. OR across different columns is harder, because no single index is sorted by both. SQLite can still combine two index searches (MULTI-INDEX OR), but only if every branch has a usable index. If one branch has no index, the whole query falls back to a full scan:

SQL OR across different columns

Press Run (or Ctrl + Enter) to see the result here.

When the planner can't combine the branches well, rewriting an OR as a UNION of two indexed queries is a classic manual fix. Use UNION (not UNION ALL) if a row could match both branches and must appear once.

Select only what you need

Two rules that sound obvious and are broken constantly:

  • Ask only for the columns you'll use. SELECT * reads and transfers every column, including large text and JSON you'll never show. It also prevents covering indexes from working, because "all columns" are never all in an index. And it makes your code fragile when someone adds a column.
  • Ask only for the rows you'll use, and let the database do the aggregating. Fetching 50,000 rows to count them in application code moves 50,000 rows over the network. SELECT COUNT(*) moves one. The same goes for sums, maximums, "does any row exist?" checks and top-N lists: use COUNT, SUM, MAX, EXISTS and ORDER BY ... LIMIT in SQL.

A related trap: when you only need to know whether something exists, COUNT(*) > 0 keeps counting after the first match, while EXISTS stops at the first one:

SQL
-- Counts every matching row, then compares
SELECT COUNT(*) > 0 FROM orders WHERE customer_id = 42 AND status = 'pending';

-- Stops at the first match
SELECT EXISTS (
  SELECT 1 FROM orders WHERE customer_id = 42 AND status = 'pending'
);

EXISTS, IN or JOIN?

"Customers who have at least one pending order" can be written three ways. They are not interchangeable:

SQL Three ways to find customers with pending orders

Press Run (or Ctrl + Enter) to see the result here.

The join returns 5,000 rows (one per pending order), while IN and EXISTS both return the 100 customers. People often "fix" the join's duplicates with SELECT DISTINCT, which makes the database produce all 5,000 rows and then throw 4,900 away with an extra temp B-tree. If you need columns from the orders, join. If you only need to filter by them, use EXISTS or IN.

Between IN and EXISTS, modern planners (SQLite, PostgreSQL, MySQL 8, SQL Server) often produce similar plans, so choose the one that reads best and check the plan when it matters. Two rules do hold everywhere:

  • EXISTS with a correlated subquery needs an index on the inner join column, here (customer_id, status). The plan above shows SEARCH o USING COVERING INDEX idx_orders_customer_status (customer_id=? AND status=?) inside the loop.
  • Prefer NOT EXISTS over NOT IN. If the subquery can return a NULL, NOT IN returns no rows at all, which is a correctness bug, not just a speed issue. See IN and BETWEEN and EXISTS.

Correlated subqueries that run thousands of times

A correlated subquery in the SELECT list runs once per outer row. That's fine with an index and painful without one. Rewriting it as a join with GROUP BY (or a window function) lets the database do one pass instead of many:

SQL Correlated subquery versus GROUP BY

Press Run (or Ctrl + Enter) to see the result here.

Look at the ms column. Version A scans all 100,000 orders once per customer: about 2 million row reads to produce 20 numbers. It typically takes about as long for 20 customers as version B takes for all 2,000, so running A for every customer would be roughly 100 times slower. B answers the whole question in one pass (its plan shows SQLite building a temporary AUTOMATIC COVERING INDEX for the join). Version C shows that a real index rescues the correlated form too: each subquery becomes a short, covering SEARCH, and all 2,000 counts usually take just a few milliseconds. In real code, prefer B's shape for reports over many rows, and make sure an index exists whichever form you choose.

The N+1 query problem

The most common performance bug in web applications isn't in any single query. It's in the number of queries. Code like this (shown in Python, but every language and ORM can do it):

Python
orders = db.execute("SELECT id, ordered_at FROM orders WHERE customer_id = ?", [7])
for order in orders:                      # 1 query...
    items = db.execute(                   # ...plus N more, one per order
        "SELECT product_id, quantity FROM order_items WHERE order_id = ?",
        [order.id],
    )

runs 1 query for the list plus N queries for the details. With 200 orders that's 201 round trips to the database, each with its own network latency and parsing overhead. The page gets slower as the customer gets more loyal. ORMs make this easy to do by accident, because order.items looks like a simple property access. They also provide the fix: "eager loading" (with() in Laravel, includes in Rails, select_related/prefetch_related in Django), which loads the related rows in one or two queries.

In SQL, the fix is a join, or a single IN query for all the details. You can even shape the result as JSON so the application receives each order with its items already nested:

SQL One query instead of N+1 ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

One round trip returns every order for customer 21 with its items. See Working with JSON for json_group_array and friends.

How to find N+1 problems: log or count the queries per request. Most frameworks have a debug toolbar or query log that shows the count. If a page runs dozens of nearly identical queries that differ only in an id, you've found one.

More habits that pay off

  • Paginate with keys, not big offsets. LIMIT 20 OFFSET 100000 still walks past 100,000 rows before returning 20. Keyset pagination (WHERE (ordered_on, id) > (?, ?) ORDER BY ordered_on, id LIMIT 20) jumps straight to the right place. See LIMIT, OFFSET and Pagination.
  • Use UNION ALL unless you need de-duplication. UNION sorts or hashes every row to remove duplicates.
  • Filter early. Put conditions in WHERE rather than HAVING when they don't involve aggregates, so rows are discarded before grouping. See HAVING.
  • Batch your writes. Inserting 10,000 rows in 10,000 separate transactions forces 10,000 disk syncs. Wrap them in one transaction, or use multi-row INSERTs.
  • Keep statistics fresh. Planners choose indexes based on statistics about your data. Run ANALYZE (SQLite, PostgreSQL) or ANALYZE TABLE (MySQL) after big data changes; PostgreSQL's autovacuum usually does it for you.
  • Don't optimize blindly. Find the queries that actually cost the most (PostgreSQL's pg_stat_statements, MySQL's slow query log, SQL Server's Query Store) and fix those first. A query that runs once a night rarely matters; one that runs on every page view always does.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. orders has an index on created_at. Which condition can use it?

    The range compares the raw column with constants, so it is sargable. The other three wrap the column in a function or expression, which hides it from the index.
  2. 2. How should you rewrite WHERE price * 1.25 > 100 to make it sargable?

    Move the arithmetic to the constant side so the indexed column stands alone. Swapping sides or rounding still leaves the column inside an expression.
  3. 3. A JOIN between customers and orders returns each customer once per order, and you only need the customer names. What's the best fix?

    You only need orders as a filter, so a semi-join with EXISTS or IN returns each customer once without producing and then discarding duplicate rows.
  4. 4. A page lists 50 blog posts and then runs one query per post to load its author. How many queries run, and what is this called?

    One query loads the list and 50 more load the authors, one per post. A join or a single IN query for all authors fixes it.
  5. 5. Which SQLite query can use a normal (case-sensitive) index on email for a prefix search?

    GLOB is case-sensitive, matching the index's ordering, so SQLite turns the prefix into an index range. Default LIKE is case-insensitive and needs a NOCASE index; a leading wildcard can never use an index.

Summary

  • Measure with query plans and timings on realistic data before and after every change.
  • Keep conditions sargable: leave indexed columns bare and move functions and arithmetic to the constant side. Use ranges instead of date functions.
  • Leading wildcards can't use an index; in SQLite, GLOB (or a NOCASE index for LIKE) enables prefix searches.
  • OR across different columns needs an index on every branch, or a rewrite as UNION.
  • Select only the columns and rows you need, and aggregate in the database.
  • Use JOIN when you need the other table's columns, and EXISTS/IN when you only filter by it; prefer NOT EXISTS over NOT IN.
  • Correlated subqueries need an index, or a rewrite into one join with GROUP BY.
  • Kill N+1 queries with joins, a single IN query or your ORM's eager loading.

Next: Partial, Expression and Covering Indexes, where you'll design indexes that fit your queries precisely.