The column you never render still costs you

2026-10-12

I was watching Cristian Planas’s RailsConf talk, A Rails Performance Guidebook, where he flags greedy selects (pulling back more columns from the database than you’ll actually use) as an antipattern. It reminded me of an endpoint at work whose p99 was way too high because of a few very heavy columns, on a screen that never even showed them. Digging into why even led meto a performance regression in MySQL itself, still unfixed in 8.4, and that’s where this post ends up.

The setup

All queries shown will be executed against a stripped-down calls table. Most of it is narrow, but a call keeps its whole transcript:

CREATE TABLE calls (
  id              BIGINT PRIMARY KEY,
  account_id      BIGINT NOT NULL,
  ...
  summary         MEDIUMTEXT,  -- 2 KB
  notes           MEDIUMTEXT,  -- 1 KB
  transcript      JSON,        -- ~290 KB
  transcript_text MEDIUMTEXT,  -- 36 KB
  KEY index_calls_on_account_id (account_id)
);

Every call belongs to an account, and the only secondary index is on account_id. As I said, I measured on MySQL 8.4 running locally, with the default 128 MB buffer pool. Production gives it far more memory, but it also has far more data than one 5 GB table, so most of the transcripts won’t be cached there either.

One account gets 15,000 calls, 7 in 8 of them completed, and started_at is shuffled against id, so the two sort orders disagree.

CREATE TABLE calls (
  id              BIGINT AUTO_INCREMENT PRIMARY KEY,
  account_id      BIGINT NOT NULL,
  title           VARCHAR(255) NOT NULL,
  status          VARCHAR(32) NOT NULL,
  started_at      DATETIME NOT NULL,
  deleted_at      DATETIME,
  summary         MEDIUMTEXT,
  notes           MEDIUMTEXT,
  transcript      JSON,
  transcript_text MEDIUMTEXT,
  KEY index_calls_on_account_id (account_id)
) ENGINE=InnoDB;

DELIMITER $$

CREATE PROCEDURE seed()
BEGIN
  DECLARE i INT DEFAULT 1;

  START TRANSACTION;
  WHILE i <= 15000 DO
    INSERT INTO calls (account_id, title, status, started_at,
                       summary, notes, transcript, transcript_text)
    VALUES (1, CONCAT('Call ', i),
            IF(i % 8 = 0, 'scheduled', 'completed'),
            '2024-01-01' + INTERVAL (i * 7919 % 15000) HOUR,
            REPEAT('s', 2000), REPEAT('n', 1000),
            JSON_OBJECT('text', REPEAT('x', 290000)),
            REPEAT('m', 36000));
    SET i = i + 1;
    IF i % 1000 = 0 THEN COMMIT; START TRANSACTION; END IF;
  END WHILE;
  COMMIT;
END$$

DELIMITER ;

CALL seed();
ANALYZE TABLE calls;

Sparse fieldsets trim the response

Our API follows the JSON:API spec, which lets a client ask for a sparse fieldset, something like fields[calls]=title,started_at, which only returns those attributes. We used it on calls to avoid serializing the heavy columns.

The problem was that the fieldset only filtered the response from the API to the client. Because it didn’t affect the database query, the query still ran SELECT *, MySQL read the transcripts, and Rails loaded them into memory.

Big values go to overflow pages

InnoDB stores a table as a B-tree ordered by the primary key, and the full rows sit in its leaf pages. A secondary index points to rows by id, so to read a row found through it, InnoDB looks the id up in that tree.

A page is 16 KB, and a row has to fit in roughly half of one. When it doesn’t, ROW_FORMAT=DYNAMIC moves the longest values out to overflow pages and leaves a 20-byte pointer in the row. Here’s where the columns of an average call end up:

Narrow columns live on the row’s page, so they are basically free once the row is read. An off-page value is one or more extra pages, and if those aren’t in the buffer pool, they are read from disk (which on an SSD is roughly a thousand times slower than memory). Row pages and overflow pages share the same buffer pool, so reading a lot of blobs pollutes it, pushing out the row pages other queries need.

Newest first: a sort the index serves

The default order is newest first (id DESC):

SELECT * FROM calls
WHERE account_id = ? AND deleted_at IS NULL AND status = 'completed'
ORDER BY id DESC
LIMIT 30

InnoDB appends the primary key to every secondary index, so index_calls_on_account_id is physically (account_id, id). The entries come out already ordered by id and MySQL walks them backwards without a filesort. But status and deleted_at aren’t in the index, so for every entry it has to fetch the row to check them.

Each call goes through the whole chain before the next one starts:

1. the next entry from the index
2. its row from the clustered index
3. its overflow pages
4. the filter on status and deleted_at
5. the limit: stop at 30, otherwise back to 1

There is no phase where everything is read first and filtered after. Seven in eight calls are completed, so the first page stops after 35 rows, and SELECT * takes about 2 ms. The blobs are read, but only for 35 rows.

This was the happy path, though. The execution time depends on how many rows get read. In a copy of the table where only the two oldest calls are completed, the same query walks all 15,000 calls to find them: 25 ms for the narrow version, 15 s for SELECT *. That is what made this case confusing for me at first: the same query was fast for some tenants and slow for others, depending on how their data was distributed.

By start time: a sort the index can’t serve

Now let’s sort the same list by started_at. It isn’t in any index, so MySQL has to visit every candidate, sort them, and only then take the first 30:

1. index lookup on the account: 15,000 rows, each read with every selected column, blobs (unfortunately) included
2. filter on deleted_at and status: 13,125 rows
3. construct (id, started_at) pairs and sort them by started_at
4. limit: 30 pairs
5. read the 30 winners (all selected columns) again by id

The narrow query, SELECT id, title, started_at:

-> Limit: 30 row(s)
     (actual time=24.5..24.5 rows=30)
  -> Sort: calls.started_at DESC, limit input to 30 row(s) per chunk
       (actual time=24.5..24.5 rows=30)
    -> Filter: (status = 'completed' and deleted_at is null)
         (actual time=0.246..23.6 rows=13125)
      -> Index lookup on calls using index_calls_on_account_id
         (account_id=1)
           (actual time=0.245..22.5 rows=15000)

The same query with SELECT id, transcript_text instead:

-> Limit: 30 row(s)
     (actual time=4034..4036 rows=30)
  -> Sort row IDs: calls.started_at DESC, limit input to 30 row(s) per chunk
       (actual time=4034..4036 rows=30)
    -> Filter: (status = 'completed' and deleted_at is null)
         (actual time=0.59..4032 rows=13125)
      -> Index lookup on calls using index_calls_on_account_id
         (account_id=1)
           (actual time=0.589..4028 rows=15000)

Interestingly enough, the sort doesn’t carry whole rows, only the (id, started_at) pairs, so it takes about 4 ms, from 4,032 to 4,036. That doesn’t save the query, though. The other 4 seconds are in the index lookup, where InnoDB reads the transcript_text of all 15,000 candidates, just for the query to return 30 of them.¹ SELECT *, which is what the endpoint actually ran, takes about 13 s.

Which columns get read

The pagination runs a COUNT(*) with the same WHERE before the main query, for the total. In production I noticed it was much faster than the list itself, even though both walk the same rows. Here it takes 30 ms against the 13 s of the SELECT *. That made no sense to me until I ran into the read_set.

Which columns InnoDB reads is decided before execution. The server builds a list of columns InnoDB reads, called the read_set, which includes NOT just the SELECT list, but also whatever is needed for filtering and sorting. For example, let’s take a look at the following query:

SELECT id, title, started_at FROM calls
WHERE account_id = ? AND deleted_at IS NULL AND status = 'completed'
ORDER BY started_at DESC
LIMIT 30

It returns three columns but reads six, id, title and started_at from the SELECT plus account_id, deleted_at and status from the WHERE. All six sit on the row page, and the query takes 25 ms. Make it SELECT * and the read_set also picks up transcript, transcript_text, summary and notes, roughly 330 KB for every row InnoDB visits.

This is why the count was fast. The star in COUNT(*) means “count rows”, not “read every column”, so its read_set holds only the three WHERE columns, all of them on the row page.

The fix at the index

First, we can reduce the number of rows read. We can achieve that quite easily with the right index. Sorted by start time, the list visits 15,000 rows to return 30 because its index stops short of both the WHERE and the ORDER BY. So let’s give it an index that covers both:

KEY (account_id, status, deleted_at, started_at)

To the index, IS NULL is just another equality, so after three equalities the entries come out already in started_at order. There’s no sort and MySQL reads 30 rows. Even a SELECT * then pays only for the blobs of the rows it returns and runs in a mere 0.9 ms.

But an index serves one query shape, and every other filter or sort on the list needs an index of its own. And the 30 rows we do return still drag their transcripts into Rails, polluting the buffer pool on the way.

The fix at the query

For the queries the index doesn’t cover, we can make each visited row cheaper instead. The direct tool is .select. If we name the columns we need, the heavy ones never enter the read_set. What we need is for the SELECT to follow the sparse fieldset:

# Every attribute that reads a heavy column names the columns it reads.
# An attribute can read a column under a different name, or several of them.
HEAVY_COLUMNS = {
  transcript:      %w[transcript],
  transcript_text: %w[transcript_text],
  highlights:      %w[transcript transcript_text],
}.freeze

def selected_columns(fields)
  # No fields[calls] in the request, nothing changes.
  return Call.column_names if fields.nil?

  # A heavy column stays as long as any requested attribute reads it,
  # the rest drop out of the SELECT.
  heavy  = HEAVY_COLUMNS.values.flatten
  needed = HEAVY_COLUMNS.values_at(*fields).compact.flatten
  Call.column_names - (heavy - needed)
end

fields = params.dig(:fields, :calls)&.split(",")&.map(&:to_sym)
Call.where(...).select(selected_columns(fields))

The plan doesn’t change and neither does the number of rows visited. Each visit just gets as cheap as in the narrow query. Sorted by start time, that’s 13 s against 25 ms.

The catch is that every call it loads is now a partial model. Anything that touches a dropped column, a callback, a cache key, a helper the serializer calls, raises ActiveModel::MissingAttributeError, and only at runtime, on the one path that happens to run with fields. And HEAVY_COLUMNS has to keep up with the serializer by hand.

Deferring the join

When the client doesn’t send fields, there’s nothing to build the SELECT from. We can still split the work in two: a narrow query finds the ids of the page, and only then are the full rows read, for those ids alone.

SELECT calls.* FROM calls
JOIN (
  SELECT id FROM calls
  WHERE account_id = ? AND deleted_at IS NULL AND status = 'completed'
  ORDER BY started_at DESC
  LIMIT 30
) page USING (id)
ORDER BY calls.started_at DESC

This is called a deferred join. The inner query visits the same 15,000 rows and sorts them, but with the narrow read_set. The outer one reads the blobs of only the 30 rows that made the page and. The result is the same as the SELECT *, and it takes 31 ms instead of 13 s.

The problem for me here was the fact that it wasn’t easy to implement on the application layer. The inner query still has to carry every filter, permission check and sort from the request.

Splitting the column out

The durable fix is to move the heavy columns out of the table, either into something like S3 or into their own table, one row for each call:

CREATE TABLE calls (
  id         BIGINT AUTO_INCREMENT PRIMARY KEY,
  account_id BIGINT NOT NULL,
  title      VARCHAR(255) NOT NULL,
  status     VARCHAR(32) NOT NULL,
  started_at DATETIME NOT NULL,
  deleted_at DATETIME,
  KEY index_calls_on_account_id (account_id)
);

CREATE TABLE call_contents (
  call_id         BIGINT PRIMARY KEY,
  summary         MEDIUMTEXT,
  notes           MEDIUMTEXT,
  transcript      JSON,
  transcript_text MEDIUMTEXT,
  FOREIGN KEY (call_id) REFERENCES calls (id)
);

For me this is the best option, because these are columns you never filter or sort by, so no list ever needs the join. On the seed, the same SELECT * sorted by start time drops from 13 s to 10 ms, faster even than the narrow query on the original table, because the 2 KB summary left the row page too.

If the blobs aren’t in the table every query reads, not even a SELECT * can read them by accident. The list, a preload, a find, a job that loads the whole model, none of them can touch a column that isn’t on the table. Unlike the fieldset trick, it doesn’t depend on the client sending fields, on someone finding every preload or on a hand-kept list of columns, and there’s no partial model to trip over. Unlike the index, it doesn’t depend on the query shape. The default becomes cheap and the expensive part is asked for explicitly, which is the show page and not much else.

The blob was already stored off-page, so this mostly lines the schema up with what the disk was doing anyway. The cost is a data migration on big tables, and a join or a second query on the paths that do need the content.

Here are all the numbers side by side:

Takeaway

Although it wasn’t the main point of this post, I think another really important lesson here is the difference between rows read and rows returned. The list returned 30 rows and read up to 15,000, each with every selected column, heavy ones included. A sparse fieldset doesn’t change that, because it trims the response and leaves the query as it was.

So when a list endpoint is slow and its plan looks fine, run a COUNT(*) with the same WHERE. If the count is fast and the SELECT * isn’t, the time goes to columns nobody asked for, usually a TEXT, BLOB or JSON. Selecting fewer columns or adding the right index fixes that one query, and moving the heavy columns into their own table should fix it for every query on the table.

Cristian was right to call greedy selects an antipattern in his talk, and hopefully I’ve convinced you of it too.