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

An index that made writes slower than the reads it saved

Deep diveApril 23, 202110 min read

Eleven indexes on a table with a heavy write path. Every index is a write amplification that nobody measured, and three of them were never used.

The cache that returned stale data for eleven days

Deep diveMarch 4, 202110 min read

A price change that took eleven days to reach one page. Three caches, and only one of them was being cleared.

LAST_VALUE is wrong unless you write the frame out

NoteNovember 26, 20202 min read

LAST_VALUE with an ORDER BY and no frame returns the current row rather than the last one, which is correct by the specification and is never what anybody…

Profiling before you optimise

Deep diveNovember 5, 202010 min read

Two days spent optimising a function that was 3% of the request. The profiler said so in ninety seconds, and nobody had run it.

A functional index, without the generated column

NoteNovember 3, 20201 min read

Indexing an expression required adding a generated column and indexing that, which means a schema change and a column nobody wants in a SELECT *. The double parentheses…

NTILE and PERCENT_RANK, for a report that wants quartiles

NoteOctober 6, 20201 min read

A report asking for the top quartile of customers by spend was a subquery computing a count, a second computing a threshold, and arithmetic in PHP. NTILE divides…

A report that was a loop, and the window function it became

Deep diveAugust 19, 202010 min read

380 MB of memory and a nightly job that could not be run on demand. Running totals and rankings needed a loop before window functions, and the loop…

pt-query-digest ranks by total time, not by slowest

NoteJuly 7, 20201 min read

The slow log ranks by individual duration, so a query taking two seconds once appears above one taking forty milliseconds two hundred thousand times. Ranking by total time…

A read replica that was silently stale

Deep diveJune 25, 202015 min read

The replica was up, accepting connections and serving data from an hour ago. Seconds_Behind_Master reported zero, honestly, and was measuring the wrong thing.

EXPLAIN ANALYZE reports what happened, not what might

NoteJune 23, 20201 min read

EXPLAIN shows the plan and the optimiser’s estimates, and the estimates are frequently wrong — a query planned for 40 rows that reads 400,000 looks entirely healthy. The…