indexing – Page 6 – Türker Yıldırım

An invisible index, to test a drop before doing it

NoteMay 25, 20212 min read

An invisible index is maintained on write and ignored by the optimiser, which makes dropping an index a reversible experiment instead of a commitment. The write cost is…

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.

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…

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…

key_len says how much of the index was used

NoteMarch 18, 20202 min read

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

ANALYZE TABLE, and statistics that were three months old

NoteFebruary 19, 20202 min read

The optimiser chooses a plan from statistics it samples, and a table whose distribution has changed since the last sample gets a plan chosen for the old data.…

Hiding an index from the optimiser before you drop it

NoteDecember 26, 20191 min read

Dropping an index that turns out to be load-bearing means rebuilding it on a large table under pressure, which is why unused-looking indexes survive for years. A week…

A collation mismatch stops a join using an index

NoteNovember 26, 20191 min read

8.0 defaults to utf8mb4_0900_ai_ci and a table converted from 5.7 is almost certainly utf8mb4_general_ci — and joining two columns with different collations cannot use an index on either.…

An index that was never used, and the week it took to prove it

Deep diveNovember 8, 201910 min read

Forty indexes on one table and nobody willing to drop any of them. Invisible indexes turn a guess into a week of production traffic as evidence.

Find indexes that have never been read

SnippetNovember 5, 20191 min read