InnoDB buffer pool versus JSON bodies in three languages

The notes grid started waiting on MySQL after the bodies grew into real articles. LaraBoom stores title, excerpt, and body as JSON objects with en, ru, and de. That shape is right for the admin form and for Vue. It is the wrong shape to SELECT * on a list.

The grid needs id, category, title, excerpt, image, size, dates. It does not need three HTML articles. On a fresh seed that is a few kilobytes and nobody cares. After the bodies grew, one list of fifty rows pulled about 1.2 MB of JSON into the InnoDB buffer pool. The JSON API then threw most of it away before the response left PHP. Vue SSR paid the same read on every public grid render.

Users felt a slow list. MySQL felt a buffer pool that looked like a cache of article HTML. Stack and news indexes lost pages to notes bodies that the tile never printed.

What the buffer pool was doing

InnoDB reads pages, not columns. A 16 KB page that contains a fat body brings that body into RAM even if the query only wanted the title. Once the page is in the pool, it stays until something else needs the space. List traffic is frequent. Detail traffic is not. The pool started looking like a cache of article HTML that the list endpoint kept rereading.

innodb_buffer_pool_size on this Docker host is 512 MB. That sounds like a lot until Redis, Memcached, php-fpm, and Node sit on the same machine. I watched Innodb_buffer_pool_reads versus Innodb_buffer_pool_read_requests while scrolling the notes page in three locales. The hit ratio was fine. The wasted RAM was not. Pages that should have held indexes for stack and news were holding notes bodies instead.

EXPLAIN looked innocent. Filter is_published, sort sort_order, covering index on paper. The index does not cover a query that still selects the body column. LaraBoom list endpoints were built for admin tables. The public grid reused them.

Split the fat column

I do not want a second database. I want the list row to be short. The body moves to note_bodies, keyed by note_id, one row, same JSON shape. The public list query stops there:

SELECT id, category_id, title, excerpt, image,
       published_at, size, sort_order, is_published
FROM notes
WHERE is_published = 1
ORDER BY sort_order ASC

The detail endpoint joins or does a second query by id. One extra query on a detail page is cheaper than fifty bodies on every grid render, including the Vue SSR pass.

Admin still loads the body, because the editor needs it. That path is rare and authenticated. I left it as a single read of the side table. I did not denormalize a locale-specific excerpt into extra columns. The excerpt JSON is small. The body JSON is the problem.

JSON extraction is not a way out

JSON_EXTRACT(body, '$.ru') looks like it would shrink the result. It does not shrink the page read. InnoDB still pulls the whole clustered record. You pay for the HTML in RAM, then throw two languages away in the SQL layer, then maybe throw the third away in PHP if the list does not even need it.

A generated column plus a secondary index helps filters. It does not help a list that should never see the body. Generated columns also copy data. Three stored generated columns for three locales would triple the worst part of the row.

• List columns stay on notes.
• HTML lives on note_bodies.
• Cache the list in Redis after the short query, not before.

After the split, the same fifty-row list was about 40 KB from MySQL. Buffer pool pages for notes looked like index pages again. SSR of the grid stopped waiting on a blob it never printed. The detail page did not get slower in a way I could measure. If it does, that is the one query I am willing to cache, per id, with a TTL that dies on save in admin.

What I took from this

I measure list payload size and which pages sit in the buffer pool, not only the hit ratio. A fine hit ratio with 1.2 MB of unused HTML is still a bad list query.

I do not trust JSON_EXTRACT or a covering index on paper while the SELECT still names body. Admin list endpoints are not a public grid.

The rule I keep: if a column is not on the tile, it does not belong in the list query. JSON for i18n is fine. JSON for i18n that includes the article is a detail resource. Cache in Redis after the short query.