Get CV
Back to business cases

MySQL

A listing that sorted JSON in MySQL

ORDER BY JSON_EXTRACT filled a temp table. Forty rows still cost a filesort over fifty thousand SKUs.

The catalog listing asked MySQL for forty published rows, ordered by the Russian title. Title lived in a JSON column with en, ru, and de. The query used JSON_EXTRACT(title, '$.ru') in ORDER BY. EXPLAIN said type ALL, Using filesort, Using temporary, rows 50000.

InnoDB read the JSON blobs to sort them. The buffer pool filled with documents the listing would throw away. PHP then decoded the same JSON again. Vue SSR received three locales for a page that showed one.

The problem was sorting a document column for a forty-row grid. Shop visitors waited on a filesort over fifty thousand SKUs. I needed a typed column and a covering index, not JSON_EXTRACT in ORDER BY.

JSON_EXTRACT in ORDER BY cannot use an index. A stored column can.
JSON_EXTRACT in ORDER BY cannot use an index. A stored column can.

The generated column

title_ru is a STORED generated column from the JSON. The listing SELECT is id, slug, price, title_ru. The index is (is_published, sort_order, id) and includes those four fields as a covering index. sort_order is a small integer the editor sets. Title sort was a habit from the admin table. The public grid never needed it.

Same forty tiles. EXPLAIN goes from filesort over the table to Using index.
Same forty tiles. EXPLAIN goes from filesort over the table to Using index.

What stayed JSON

The admin form still writes the object. One locale at a time in the UI, one JSON document in the row. The public path does not parse it. Horizon still builds the Memcached slab from the generated columns, not from json_decode() in the web worker.

  • innodb_buffer_pool_size stopped being a dump of catalog JSON.
  • Handler_read_rnd_next on that query dropped off the graphs.
  • The listing query no longer allocates a temp table on disk when the sort does not fit in sort_buffer.

JSON is a write format. A listing is a range scan. Mixing the two in one SELECT is how a shop of fifty thousand SKUs looks like a full table read.

What I took from this

EXPLAIN on the listing query is the first tool. type ALL plus filesort on a JSON extract is not a cache problem. It is a schema problem.

Generated columns let the admin keep JSON and let the public path keep a btree. I will not sort extract() on the hot path again.

The public grid sorts by editor order. Title sort belongs in the admin table, not in the shop SELECT.

Back to business cases