There’s a category of SQL that’s perfectly correct, passes every code review, and works great in development. Then the table hits a few million rows and everything falls over. Not because the query is wrong — it returns exactly the right data. It just does it in a way that doesn’t scale.

The frustrating part is that these patterns are well-known. They show up in the same forms across every codebase, every ORM, every database engine. They’re easy to write, hard to spot in review, and expensive to fix once the table is large enough to make them visible.

Can adding one column to a query really hurt performance?

Everyone knows SELECT * is bad practice. That’s not the interesting part. The interesting part is what happens when a query that’s been running fine for months gets one more column added to its select list.

Say you have an orders table with a composite index on (customer_id, status, created_at) and a query that’s been in production for a year:

1

SELECT status, created_at FROM orders WHERE customer_id = 42;

This query never touches the table. The index contains every column the query needs — customer_id for the filter, status and created_at for the output. The database reads the index, returns the results, done. This is called an index-only scan (or a “covering index” query), and it’s one of the most significant performance optimizations a database can make.

Then a feature request comes in: “we need to show the order total on this page.” The change looks trivial:

1

SELECT status, created_at, total_cents FROM orders WHERE customer_id = 42;

One column added. The query is still correct. The index on (customer_id, status, created_at) still helps with filtering. But total_cents isn’t in the index — so now, for every matching row, the database has to follow a pointer from the index back to the table to fetch that one extra column. That’s a random I/O operation per row. On a table with millions of rows, this can turn a sub-millisecond query into one that takes seconds.

The execution plan tells the story:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

-- Before: index-only scan
EXPLAIN ANALYZE SELECT status, created_at FROM orders WHERE customer_id = 42;
-- Index Only Scan using idx_orders_cust_status_created on orders
-- Heap Fetches: 0
-- Execution Time: 0.4 ms

-- After: index scan + table lookups
EXPLAIN ANALYZE SELECT status, created_at, total_cents FROM orders WHERE customer_id = 42;
-- Index Scan using idx_orders_cust_status_created on orders
-- Execution Time: 1243.7 ms

Same index. Same filter. One extra column in the select list — and a three-order-of-magnitude difference in execution time.

This is the mechanism behind why SELECT * is expensive, but the important thing is that it’s not limited to SELECT *. Any query can lose its covering index by adding a column that isn’t in the index. The fix is either to only select what’s needed, or to extend the index to cover the new column — which has its own write-performance trade-off.

Buffer pool pollution compounds the damage. When the database has to fetch full rows from the table instead of reading compact index entries, it loads entire data pages into the buffer pool. Those pages — full of columns the query doesn’t need — evict pages that other queries do need. On a busy system with a finite buffer pool, one query losing its covering index can degrade performance for unrelated queries across the entire database.

The takeaway isn’t “never add columns to queries.” It’s that the select list is part of the query’s performance contract with the index. Changing it can silently invalidate the optimization that made the query fast in the first place — and unlike a missing WHERE clause or a bad JOIN, nothing in the query results will tell you something went wrong. It just got slower.

Non-SARGable predicates

A predicate is “SARGable” (Search ARGument able) if the database can use an index to evaluate it. Wrapping a column in a function makes the predicate non-SARGable — the engine has to evaluate the function on every row before it can filter, which means a full table scan regardless of what indexes exist.

1
2
3
4
5
6
7

-- Non-SARGable: index on created_at is useless
SELECT id, status FROM events
WHERE YEAR(created_at) = 2025;

-- SARGable: index on created_at works
SELECT id, status FROM events
WHERE created_at >= '2025-01-01' AND created_at < '2026-01-01';

Both return the same rows. The first one scans every row in the table. The second one does an index range scan — orders of magnitude faster on a large table.

Common forms of this:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

-- Function on column: full scan
WHERE LOWER(email) = '[email protected]'
WHERE DATE(created_at) = '2025-01-15'
WHERE CAST(price AS INT) > 100
WHERE CONCAT(first_name, ' ', last_name) = 'Alice Smith'

-- Equivalent SARGable versions
WHERE email = '[email protected]'  -- if collation is case-insensitive
WHERE created_at >= '2025-01-15' AND created_at < '2025-01-16'
WHERE price > 100  -- fix the type at the schema level
WHERE first_name = 'Alice' AND last_name = 'Smith'

The case-sensitivity example has a trade-off: if the collation is case-sensitive and you genuinely need case-insensitive matching, you either need a functional index (PostgreSQL supports these, MySQL 8.0+ supports expression indexes) or a generated column with an index on it. Neither is free — but both are cheaper than scanning a 200 million row table.

1
2
3
4
5
6

-- PostgreSQL: functional index
CREATE INDEX idx_users_email_lower ON users (LOWER(email));
-- Now WHERE LOWER(email) = '...' uses the index

-- MySQL 8.0+: expression index
ALTER TABLE users ADD INDEX idx_email_lower ((LOWER(email)));

Implicit type conversions are a subtler version of the same problem. If account_id is a VARCHAR and you query WHERE account_id = 12345 (numeric literal), the database may implicitly cast every row’s account_id to a number for comparison — killing index usage. The fix is matching types: WHERE account_id = '12345'.

Random UUIDs as primary keys

UUIDv4 is a popular choice for primary keys — it’s globally unique, can be generated anywhere without coordination, and doesn’t leak information about row count or insertion order. The trade-off is what it does to your indexes.

B-tree indexes are sorted structures. When the primary key is an auto-incrementing integer, every new row goes to the end of the index. The rightmost leaf page is the only one that gets written to. The rest of the index stays in cache, undisturbed. Inserts are sequential and fast.

UUIDv4 is random by design. Every new row lands at a random position in the index. Instead of appending to one page, the database has to find the right page somewhere in the middle of the tree, load it into the buffer pool if it’s not already there (it usually isn’t), split it if it’s full, and write it back. On a table with hundreds of millions of rows, the index doesn’t fit in memory — so most inserts trigger a random disk read.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

-- Auto-increment: every insert goes to the end of the index
-- Pages are sequential, writes are fast, cache is efficient
CREATE TABLE orders (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    ...
);

-- UUIDv4: every insert lands at a random position in the index
-- Pages fragment, cache thrashes, writes slow down at scale
CREATE TABLE orders (
    id CHAR(36) PRIMARY KEY,  -- or BINARY(16)
    ...
);

The impact isn’t just on the primary key index. In InnoDB (MySQL), every secondary index includes a copy of the primary key. A 36-byte CHAR(36) UUID in every secondary index entry means larger indexes, more pages, more I/O — compared to an 8-byte BIGINT. The secondary indexes on a UUID-keyed table can be 3-4x larger than the same indexes on a BIGINT-keyed table.

Page splits are the other cost. When a new UUID lands in a full page, InnoDB splits the page in two — each roughly half full. Over time, with random inserts, the index develops internal fragmentation. Pages are allocated but only partially used. The index is physically larger than it needs to be, and scans have to read more pages to cover the same number of rows.

PostgreSQL handles this differently — its heap storage means the primary key is just another index, so the physical table isn’t ordered by it. But the primary key index itself still suffers from the same random insertion pattern, and the write amplification from random page loads still applies.

There are a few ways to get the benefits of UUIDs without paying the index penalty:

UUIDv7 and ULID embed a timestamp prefix, making the values roughly time-ordered. New rows sort near the end of the index, similar to auto-increment, while still being globally unique and externally generatable. You get coordination-free generation without the random write penalty.

1
2
3
4
5
6

-- UUIDv7: time-ordered, so inserts are roughly sequential
-- PostgreSQL 17+ has built-in uuidv7() support
CREATE TABLE orders (
    id UUID PRIMARY KEY DEFAULT uuidv7(),
    ...
);

UUID-to-integer mapping keeps UUIDs at the edge and uses integers internally. A single lookup table maps the external UUID to an internal BIGINT, and every other table in the database uses the BIGINT as its foreign key. The UUID lookup happens once — at the API boundary — and everything downstream is fast, compact, 8-byte integer joins.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16

CREATE TABLE users (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    external_id BINARY(16) NOT NULL UNIQUE,  -- the UUID the outside world sees
    ...
);

-- Every other table references the BIGINT, not the UUID
CREATE TABLE orders (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT NOT NULL REFERENCES users(id),
    ...
);

-- API request comes in with a UUID — one indexed lookup to resolve it
SELECT id FROM users WHERE external_id = UUID_TO_BIN('a1b2c3d4-...');
-- From here on, everything uses the BIGINT

The UUID column has a unique index, so the lookup is a single index seek — sub-millisecond regardless of table size. The rest of the schema gets 8-byte keys everywhere: smaller indexes, faster joins, no page splits, no secondary index bloat. The external-facing API still uses UUIDs, so you don’t leak sequence information or row counts.

The trade-off is an extra layer of indirection. Every inbound request needs to resolve the UUID before it can do anything else. In practice this is negligible — it’s one indexed lookup — but it does mean your schema has two identity systems to maintain.

Putting it together

The common thread across these patterns is that none of them produce errors. The queries are syntactically correct, logically correct, and return the right results. They just do it in a way that doesn’t survive contact with real data volumes. A query that takes 3ms on 10,000 rows and 45 minutes on 200 million rows is the same query — the only thing that changed is the data.

EXPLAIN ANALYZE is the single most useful tool for catching these issues before they reach production. It shows what the database actually does — not what you think it does. Reading execution plans isn’t glamorous, but it’s the difference between a query that works and a query that works at scale.