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

Reading the slow log with pt-query-digest rather than by eye

NoteMay 21, 20192 min read

The slow log ranks by individual duration, so a query taking two seconds once appears above one taking 40 milliseconds two hundred thousand times — and the second…

Elasticsearch 7 removed mapping types

Deep diveApril 30, 201910 min read

One type per index in 6, none at all in 7, and every query URL changes. The reindex cannot be done in place — and the default shard…

Product lookup tables, and reading a shop without postmeta

Deep diveApril 10, 201910 min read

3.6 added lookup tables, which is WooCommerce admitting postmeta does not scale. What they contain, what they do not, and the regeneration nobody schedules.

ROW_NUMBER needs a tiebreak or it is not deterministic

NoteMarch 26, 20192 min read

When two rows compare equal under the window’s ORDER BY, the numbering between them is arbitrary and MySQL is free to choose differently on the next execution. The…

A descending index is real in 8.0 and matters for one shape

NoteFebruary 28, 20191 min read

Earlier versions parsed DESC in an index definition and ignored it, which is worse than rejecting it — the index looked right and the query did a filesort.…

key_len tells you how much of a composite index was used

NoteJanuary 16, 20192 min read

EXPLAIN saying an index was used does not mean all of it was, and the column that says how much is the one nobody reads. The number is…

The leftmost prefix rule, in one example

NoteNovember 29, 20181 min read

A composite index on (a, b, c) serves queries filtering on a, on a and b, and on all three — and does nothing for a query filtering…

SCAN with a MATCH pattern is still O(n) overall

NoteNovember 5, 20182 min read

SCAN is the safe replacement for KEYS because each call does bounded work, and the pattern is applied after the keys are fetched — so scanning a million…

Count queries per request in Laravel middleware

SnippetOctober 17, 20181 min read

A test that asserts on a query count

NoteOctober 15, 20182 min read

An N+1 does not fail a test — the page renders correctly, just slowly — so the regression ships and is found by a customer three releases later.…