Speeding Up Slow SQL: Index Design, Reading EXPLAIN, N+1 Queries, Joins and Subqueries

By 0xmote · Apr 13, 2026 · 20 min read · Updated Sep 27, 2026

Key takeaways

Most slow queries come from a full scan where an index was expected, or from an ORM repeating the same query hundreds of times. This post covers how indexes work, the conditions that stop an index from being used, N+1 fixes, common myths about joins and subqueries, and a workflow for finding, fixing, and re-measuring slow queries.

What this post covers

Fixing a slow query usually follows the same loop: find it, read its execution plan, change an index or the query, and measure again. This post covers the background you need for that loop (how B-Tree indexes work) and the traps that come up most often in practice: indexes that exist but are not used, N+1 queries, and OFFSET pagination.

I have deliberately left timings out of the examples. The same query can differ by orders of magnitude depending on data size, distribution, cache state, and hardware, so what matters is how the execution plan changes. Measure improvements on your own data.

Why indexes are fast

Without an index

Without an index, the database has to read every row in the table to find the matching ones (a full table scan). The cost grows with the number of rows.

SELECT * FROM users WHERE email = '[email protected]';
-- Without an index on email, every row's email is compared

B+Tree indexes

The default index in MySQL (InnoDB) and PostgreSQL is a B+Tree. Keys are stored in sorted order in a tree, so the database walks down a few levels from the root to reach the leaf page that holds the key.

                 [Root: m | t]
               /       |       \
     [a | d | g]   [m | p | s]   [t | w | z]
         ...           |             ...
                 [mike → PK 5, peter → PK 6, sam → PK 7]  ← leaf page
                       ↔ linked to neighboring leaves (good for ranges)

Three properties matter:

A page (16KB by default in InnoDB) holds hundreds of keys, so the tree is very shallow. Tables with millions to hundreds of millions of rows typically have a tree height of 3 or 4, so a lookup reads only a handful of pages.
Leaf pages are linked, so range operations such as BETWEEN, >=, and ORDER BY can read keys in order.
Because keys are sorted, only conditions that match from the left (leading) columns can narrow the search. Most of the “index not used” cases later in this post follow from this.

Clustered and secondary indexes

In InnoDB, the table data itself is stored in a B+Tree ordered by primary key (the clustered index). A secondary index such as email stores the primary key value in its leaves, not the whole row, so a lookup through a secondary index needs a second lookup in the clustered index to fetch the row.

Secondary index (email)          Clustered index (PK)
alice@... → PK 1   ──────→      PK 1 → (id, name, email, created_at ...)

If every column the query needs is inside the index (a covering index), the second lookup can be skipped. PostgreSQL is structured differently (the table is a heap and indexes point at row locations), but it has the same optimization in Index Only Scans.

Creating indexes

-- Single-column index
CREATE INDEX idx_email ON users(email);
-- Composite index
CREATE INDEX idx_user_created ON orders(user_id, created_at);
-- Unique index
CREATE UNIQUE INDEX idx_email_unique ON users(email);

-- Drop and list (MySQL)
DROP INDEX idx_email ON users;
SHOW INDEX FROM users;
-- Drop and list (PostgreSQL)
DROP INDEX idx_email;
-- in psql: \d users

Indexes are not free. Every INSERT, UPDATE, and DELETE updates them too, and they take disk space and buffer pool memory. Write-heavy tables that accumulate “just in case” indexes get noticeably slower to write.

Reading EXPLAIN

EXPLAIN shows the order and method the optimizer plans to use.

EXPLAIN SELECT * FROM orders WHERE user_id = 123;

MySQL columns to check

+----+-------------+--------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+--------+------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | orders | ALL  | NULL          | NULL | NULL    | NULL | 498213 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+--------+-------------+

type: the access method. Roughly, const (one row by PK or unique key) → eq_ref → ref (several rows through an index) → range (index range) → index (full index scan) → ALL (full table scan), in increasing amount of data read. index is not always bad: reading a covering index end to end (Extra: Using index) is much lighter than reading the table. ALL on a small table is not a problem either.
key: the index actually chosen. If an index appears in possible_keys but key is NULL, the optimizer decided a full scan was cheaper, which is common when the condition matches a large share of the table.
rows: the estimated number of rows to read, based on statistics. It can be wrong.
Extra: common values are Using filesort (a separate sort because index order could not be used), Using temporary (a temporary table), and Using index (covering index).

After adding an index, check that the plan changes:

CREATE INDEX idx_user_id ON orders(user_id);
EXPLAIN SELECT * FROM orders WHERE user_id = 123;
-- type: ref, key: idx_user_id, rows: roughly that user's order count

Seeing actual numbers instead of estimates

-- PostgreSQL: executes the query and prints actual rows and time per step
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE user_id = 123;

-- MySQL 8.0.18+: tree-format output with actual execution data
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 123;

ANALYZE really runs the query. On UPDATE or DELETE it changes data, so wrap it in a transaction and roll back. The first thing I look at in a plan is the gap between estimated rows and actual rows. If they are off by a factor of tens or more, refresh statistics with ANALYZE TABLE (MySQL) or ANALYZE (PostgreSQL) before adding any index.

Designing composite indexes

Column order

SELECT * FROM orders
WHERE user_id = 123
  AND status = 'completed'
  AND created_at >= '2026-01-01';

-- With the range column first, the next two columns cannot narrow the search well
CREATE INDEX idx_bad ON orders(created_at, status, user_id);
-- Equality columns first, range column last
CREATE INDEX idx_good ON orders(user_id, status, created_at);

The rules:

Put equality (=) columns first. Among equality columns, the order is decided less by lookup efficiency than by reuse: if other queries filter only on WHERE user_id = ?, put user_id first so they can use the same index.
Put range columns last. Columns after a range condition cannot narrow the search; at best they are used for filtering inside the index (MySQL’s Index Condition Pushdown).
Consider ORDER BY. For WHERE user_id = ? ORDER BY created_at DESC LIMIT 20, one (user_id, created_at) index handles both the filter and the sort and removes Using filesort.

Skipping the leading column

CREATE INDEX idx_abc ON t(a, b, c);
SELECT * FROM t WHERE a = 1 AND b = 2;  -- matches from the left, index usable
SELECT * FROM t WHERE b = 2;            -- leading column a is missing, usually no range search

It is like a phone book sorted by last name: you cannot look people up by first name alone. There are exceptions, such as MySQL 8.0.13+ Skip Scan when the leading column has few distinct values, but it is not something to design around.

Conditions that stop an index from being used

These are the usual reasons for a full scan despite an index. What they have in common is that the query does not compare against the value as stored in the index.

Functions or arithmetic on the column

-- The index is sorted by created_at, not by YEAR(created_at)
SELECT * FROM orders WHERE YEAR(created_at) = 2026;
-- Rewrite as a range and the index is usable
SELECT * FROM orders
WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01';

-- The same goes for arithmetic on the column side
SELECT * FROM users WHERE id + 1 = 2;   -- avoid
SELECT * FROM users WHERE id = 2 - 1;   -- move it to the constant side

If you really need the function, index its result. The syntax differs:

-- PostgreSQL: expression index
CREATE INDEX idx_email_lower ON users (LOWER(email));
-- MySQL 8.0.13+: functional key part (note the double parentheses)
CREATE INDEX idx_email_lower ON users ((LOWER(email)));

-- The query must use the same expression
SELECT * FROM users WHERE LOWER(email) = '[email protected]';

LIKE with a leading wildcard

SELECT * FROM users WHERE email LIKE 'alice%';        -- prefix: index range scan possible
SELECT * FROM users WHERE email LIKE '%@gmail.com';   -- no known prefix, sort order is useless

In PostgreSQL, even LIKE 'alice%' may need the C collation or an index built with text_pattern_ops to use the index. If you need infix or suffix matching often:

PostgreSQL: a GIN trigram index from the pg_trgm extension accelerates LIKE '%alice%' and ILIKE directly.
MySQL: a FULLTEXT index searches by word (token), which is not the same as substring LIKE. For suffix matches such as email domains, storing the domain in its own indexed column is simpler.
If search is central to your product, a search engine such as Elasticsearch or OpenSearch is an option.

OR conditions

SELECT * FROM users WHERE id = 1 OR name = 'Alice';

If one side of the OR (name) has no index, the whole table has to be read for that condition, so you get a full scan. If both sides are indexed, MySQL can use an index merge and PostgreSQL a BitmapOr. When the optimizer cannot combine them, you can split the query:

SELECT * FROM users WHERE id = 1
UNION
SELECT * FROM users WHERE name = 'Alice';

UNION ALL avoids the deduplication cost, but a row that matches both conditions will then appear twice. Use UNION when results can overlap and UNION ALL only when you are sure they cannot.

Implicit type conversion

-- phone is VARCHAR; comparing to a number makes MySQL convert each column value
SELECT * FROM users WHERE phone = 5551234567;     -- index unusable
SELECT * FROM users WHERE phone = '5551234567';   -- matching types, index usable

The same thing happens in joins when key types, character sets, or collations differ. If user_id is INT in one table and VARCHAR in the other, or one table is utf8mb3 and the other utf8mb4, the index may not be used for the join.

The N+1 problem

N+1 means one query for a list, then one more query per row for related data. Each query is fast, but the number of round-trips grows with the number of rows.

// 1 query for 100 users + 100 queries for post counts = 101 round-trips
const users = await db.query('SELECT id, name FROM users LIMIT 100');
for (const user of users) {
  const rows = await db.query(
    'SELECT COUNT(*) AS count FROM posts WHERE user_id = ?',
    [user.id]
  );
  user.postCount = rows[0].count;
}

Fix 1: JOIN and GROUP BY

SELECT u.id, u.name, COUNT(p.id) AS post_count
FROM (SELECT id, name FROM users ORDER BY id LIMIT 100) u
LEFT JOIN posts p ON p.user_id = u.id
GROUP BY u.id, u.name;

With LIMIT on the outside, the database would join and aggregate all users and posts before cutting to 100, so pick the 100 users first and then join. posts.user_id needs an index.

Fix 2: one IN query

const users = await db.query('SELECT id, name FROM users ORDER BY id LIMIT 100');
const ids = users.map(u => u.id);
const counts = await db.query(
  'SELECT user_id, COUNT(*) AS count FROM posts WHERE user_id IN (?) GROUP BY user_id',
  [ids]
);
const countMap = Object.fromEntries(counts.map(r => [r.user_id, r.count]));
users.forEach(u => { u.postCount = countMap[u.id] ?? 0; });
// 101 round-trips → 2

When the IN list grows into the thousands, parsing cost and database-specific limits kick in, so send it in chunks.

In ORMs

// Sequelize: eager loading with include
const users = await User.findAll({ include: [{ model: Post }] });

# Django
User.objects.select_related('profile')   # 1:1, N:1 → one JOIN
User.objects.prefetch_related('posts')   # 1:N, N:N → one extra IN query

JOIN-based eager loading of a one-to-many relation repeats each user row once per post. For relations with many children, fetching them separately with an IN query, as prefetch_related does, is usually lighter. When I hunt for N+1 problems, I turn on the ORM’s query log and count the queries a single request sends. Seeing the same query shape repeated in the log is much faster than reading the code.

Myths about joins and subqueries

Table order in FROM

You often hear “put the small table first in FROM”, but the MySQL and PostgreSQL optimizers choose the order of INNER JOINs themselves based on statistics. Rewriting the order in the query usually leaves the plan unchanged. What actually matters:

An index on the join key. Usually the inner (repeatedly probed) table’s join column needs one, for example orders.user_id. Primary keys are already indexed.
Filters applied through an index early. If WHERE u.country = 'KR' removes most rows, an index on users.country reduces the rows to join.

(PostgreSQL starts following the written order once the number of joined tables exceeds join_collapse_limit, so order can matter for very large joins.)

”Filter first in a subquery”

-- On modern optimizers these usually produce the same plan
SELECT * FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.created_at >= '2026-01-01';

SELECT * FROM users u
JOIN (SELECT * FROM orders WHERE created_at >= '2026-01-01') o ON u.id = o.user_id;

The optimizer applies WHERE conditions before the join (predicate pushdown), so wrapping them in a derived table is unnecessary. Old MySQL versions (5.6 and earlier) materialized derived tables into temporary tables first, which could make the second form slower. What you need is an index on orders.created_at (or (user_id, created_at)).

Correlated subqueries

SELECT u.name,
       (SELECT COUNT(*) FROM posts p WHERE p.user_id = u.id) AS post_count
FROM users u;

Conceptually the subquery runs once per outer row. With an index on posts.user_id each run is short, which is fine for a small set of users; for all users, a single JOIN + GROUP BY aggregation is usually better. Check the plan to decide.

EXISTS vs IN

SELECT * FROM users u WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);
SELECT * FROM users u WHERE u.id IN (SELECT user_id FROM orders);

“EXISTS is faster” comes from old MySQL (5.5 and earlier), where IN (subquery) was executed poorly. MySQL 5.6+ and PostgreSQL turn both into semi-joins, so there is usually no difference. The case that does differ is NOT IN: if the subquery returns even one NULL, NOT IN returns no rows, so write negative conditions with NOT EXISTS.

Case: pagination

The problem with OFFSET

SELECT * FROM posts
ORDER BY created_at DESC
LIMIT 20 OFFSET 100000;

OFFSET reads the skipped rows and throws them away. Even with an index, later pages read more and more rows.

Keyset (cursor) pagination

Remember the sort key of the last row seen and continue from there. created_at can have duplicates, so add the unique id to the sort key, otherwise rows can be skipped or repeated.

CREATE INDEX idx_posts_created_id ON posts (created_at DESC, id DESC);

-- First page
SELECT * FROM posts ORDER BY created_at DESC, id DESC LIMIT 20;

-- Next page: pass (created_at, id) of the last row of the previous page
SELECT * FROM posts
WHERE (created_at, id) < ('2026-03-31 10:00:00', 98765)
ORDER BY created_at DESC, id DESC
LIMIT 20;

Row-value comparison (a, b) < (x, y) uses the index well in PostgreSQL. If MySQL produces a poor plan, expand it to created_at < ? OR (created_at = ? AND id < ?). The trade-off is that you cannot jump straight to page 37, so it suits infinite scroll and “next” buttons.

Case: COUNT and dashboard aggregates

Exact COUNT(*)

Because of MVCC, InnoDB does not store an exact row count, so SELECT COUNT(*) FROM posts reads the smallest index to the end. PostgreSQL also counts rows. Running this on a large table for every request adds up.

Three options:

-- 1) Estimates from statistics (not exact)
-- MySQL: InnoDB's TABLE_ROWS is a sampled estimate and can be far off
SELECT TABLE_ROWS FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'mydb' AND TABLE_NAME = 'posts';
-- PostgreSQL: estimate as of the last VACUUM/ANALYZE
SELECT reltuples::bigint FROM pg_class WHERE relname = 'posts';

// 2) Cache it if a slightly stale value is acceptable
const cached = await redis.get('posts:count');
if (cached !== null) return Number(cached);
const [{ count }] = await db.query('SELECT COUNT(*) AS count FROM posts');
await redis.set('posts:count', count, { EX: 3600 });
return count;

A counter table: if you need exact values often, update a counter row when posts are added or deleted. That can create write contention, so under heavy traffic split the counter across several rows or apply updates asynchronously.

Does merging dashboard queries make them faster?

SELECT
  (SELECT COUNT(*) FROM users) AS total_users,
  (SELECT COUNT(*) FROM orders) AS total_orders,
  (SELECT SUM(amount) FROM orders WHERE status = 'completed') AS revenue,
  (SELECT COUNT(*) FROM orders WHERE created_at >= CURDATE()) AS today_orders;

A common “optimization” merges this into one users LEFT JOIN orders query. It usually backfires. The four scalar subqueries can each use the index that suits them (created_at for today’s orders, (status, amount) for revenue), while the join reads both tables in full, joins them, and then aggregates. For dashboards like this, the effective changes are:

Check that each subquery uses an index.
Cache the result for a few minutes if second-level accuracy is not needed.
Precompute period aggregates into a summary table in a batch job.

Principles for writing queries

Select only the columns you need

SELECT * FROM users;               -- every column
SELECT id, name, email FROM users; -- only what you need

Why SELECT * hurts depends on the situation. Large TEXT, BLOB, or JSON columns inflate transfer size and memory, and a query that could be answered from a covering index ends up reading the table. On the other hand, reading one row by primary key from a narrow table makes almost no difference. The more common problem is maintenance: when columns are added, the application receives data it does not know about, and code that depends on column order can break.

DISTINCT vs GROUP BY

SELECT DISTINCT user_id FROM orders;
SELECT user_id FROM orders GROUP BY user_id;

For plain deduplication without aggregates, MySQL and PostgreSQL process these essentially the same way, and both can use an index on user_id. There is no performance reason to switch; use whichever states the intent. The habit to watch for is hiding duplicates created by a JOIN with DISTINCT. In that case EXISTS is usually both more accurate and reads fewer rows.

UNION vs UNION ALL

UNION sorts or hashes to remove duplicates. Switch to UNION ALL only when the two results cannot overlap (for example, disjoint primary key ranges from different tables) or duplicates are acceptable. It can change the result, so do not switch for performance alone.

Managing indexes

Unused indexes

-- MySQL: sys schema (based on statistics since the last restart)
SELECT * FROM sys.schema_unused_indexes WHERE object_schema = 'mydb';

-- PostgreSQL: indexes never scanned since statistics were last reset
SELECT schemaname, relname AS table_name, indexrelname AS index_name, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

Both reflect only the period since statistics collection started. Indexes used by occasional jobs, such as month-end reports, can show up here, so watch for a long enough period before dropping anything. Unique indexes enforce constraints even if no query reads them, so do not drop those.

Index size

-- MySQL: stat_name = 'size' is a page count
SELECT table_name, index_name,
       ROUND(stat_value * @@innodb_page_size / 1024 / 1024, 2) AS size_mb
FROM mysql.innodb_index_stats
WHERE database_name = 'mydb' AND stat_name = 'size'
ORDER BY size_mb DESC;

-- PostgreSQL
SELECT relname AS table_name, indexrelname AS index_name,
       pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(indexrelid) DESC;

Workflow

Step 1: find slow queries

-- MySQL: slow query log
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;   -- log queries taking 1 second or more
SHOW VARIABLES LIKE 'slow_query_log_file';

In PostgreSQL, log_min_duration_statement logs slow queries and the pg_stat_statements extension shows cumulative time and call counts per query shape. The real load often comes from queries that are called very often and add up, not the single slowest query, so sort by total time. In MySQL, the sys.statement_analysis view gives similar information.

Step 2: read the plan

EXPLAIN SELECT * FROM orders
WHERE user_id = 123 AND created_at >= '2026-01-01';
-- check type, key, rows, Extra

Step 3: change the index or the query

CREATE INDEX idx_user_created ON orders(user_id, created_at);

Step 4: measure again

Run EXPLAIN ANALYZE on the same query and check that both the plan and the actual time changed. The first run is often slow because data is not cached yet, so run it several times before comparing.

Step 5: apply in production

-- MySQL: online DDL (InnoDB, 5.6+). If it cannot be done online, it errors instead of locking
CREATE INDEX idx_user_created ON orders(user_id, created_at) ALGORITHM=INPLACE, LOCK=NONE;

-- PostgreSQL: build without blocking writes (cannot run inside a transaction block)
CREATE INDEX CONCURRENTLY idx_user_created ON orders(user_id, created_at);

If CREATE INDEX CONCURRENTLY fails, it leaves an INVALID index behind; check with \d orders, drop it, and try again. MySQL online DDL still needs a brief metadata lock at the end, so a long-running open transaction on the table makes the DDL wait, and every query behind it queues up too. Before working on a large table, check for long-running transactions.

MySQL and PostgreSQL settings worth knowing

MySQL

The query cache was removed in MySQL 8.0. Even in 5.7 and earlier it was commonly disabled on write-heavy servers because invalidation contention made things slower.
innodb_buffer_pool_size keeps data and indexes in memory; on a dedicated database server it usually gets a large share of physical memory. It can be changed without a restart since 5.7.5.
max_connections is a cap on concurrent connections, not a connection pool. Raising it alone increases per-thread memory, so tune it together with the application’s pool size.

PostgreSQL

ANALYZE users;         -- refresh statistics
VACUUM ANALYZE users;  -- clean up dead tuples + refresh statistics

# postgresql.conf (set values for your server memory and workload)
shared_buffers        # a common starting point is around 1/4 of physical memory
effective_cache_size  # estimate including the OS cache; only used for planner cost estimates
work_mem              # memory per sort/hash operation; multiplied by concurrent queries × operations, so large values are risky

With autovacuum on, most of this happens automatically, but running ANALYZE manually right after a bulk load or bulk delete helps avoid bad plans.

FAQ

Q1. Are more indexes better? No. Each index adds write cost and storage. Keep the indexes that frequent queries actually use, and look for indexes that share leading columns. If both (user_id) and (user_id, created_at) exist, the first is usually redundant.

Q2. Is an index useless on a column with few distinct values? Usually it helps little. If a value matches half the table, the optimizer will judge a full scan cheaper than an index lookup followed by table reads. Skewed distributions are different: if status is mostly done and only a few rows are pending, an index works well for WHERE status = 'pending', and in PostgreSQL a partial index WHERE status = 'pending' keeps it small. Low-cardinality columns can also be useful as the leading part of a composite index.

Q3. Composite or single-column indexes? Look at the query patterns. If user_id and status are always used together, one (user_id, status) index is better, and it also serves WHERE user_id = ? alone. Add a separate status index only if status is often queried on its own.

Q4. I added an index and it’s still slow. First check with EXPLAIN whether the index is chosen at all. If not, look at the causes in section 4 (functions, leading wildcards, OR, type conversion, missing leading column), whether the condition matches so many rows that a full scan is cheaper, and whether statistics are stale. If the index is used and the query is still slow, it may be finding many rows that each need a table lookup (consider a covering index), sorting (Using filesort), or waiting on locks.