query-optimization – Page 5 – Türker Yıldırım

The queue table that became a bottleneck at four million rows

Deep diveJune 3, 202210 min read

A database-backed queue that worked for four years. SELECT FOR UPDATE SKIP LOCKED, and a lock wait timeout.

The report that ran on a replica and was three minutes stale

Deep diveMay 31, 202210 min read

Read routing added for capacity, and a report that disagreed with the dashboard. Two numbers, both correct, three minutes apart.

ngram parsing, for a language that does not use spaces

NoteApril 20, 20222 min read

The default parser splits on whitespace, which produces one enormous token for a language that does not have any. The size of the resulting index is the cost…

The N+1 that was in the authorisation layer

Deep diveApril 4, 202210 min read

A policy check that loads the model’s owner, per row. 200 queries on a page with 50 items and correct eager loading.

Boolean mode, and the relevance that is not a ranking

NoteMarch 10, 20221 min read

Boolean mode has no fifty per cent threshold and no meaningful relevance score, so results come back in whatever order the index produced them. The scoring being a…

Full-text search that did not need a search engine

Deep diveMarch 3, 202210 min read

A search cluster running for one feature on 40,000 rows. The operational cost was larger than the feature it served.

A keyword field is not a text field, and sorting proves it

NoteFebruary 16, 20222 min read

A text field is analysed into tokens and cannot be sorted or aggregated; a keyword field is stored whole and cannot be searched for a word inside it.…

LATERAL, for a top-N per group without a window function

NoteJanuary 21, 20221 min read

A correlated derived table can reference columns from the row it is joined against, which expresses top-N-per-group directly. A window function with ROW_NUMBER reads more clearly and computes…

The report that should have been a materialised view

Deep diveOctober 21, 202110 min read

MySQL has no materialised views and the report needs one anyway. A summary table, a watermark, and the backdated edit that breaks both.

A migration that ran for four hours on a table nobody could lock

Deep diveSeptember 23, 202114 min read

An ALTER on forty million rows and a maintenance window nobody would approve. Online DDL is online until it is not.