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

A composite index in the wrong column order

NoteNovember 23, 20232 min read

An index on (created_at, status) serving a query that filters on status and ranges on date, which uses the first column and stops. The rule is equality columns…

A soft delete that broke a unique index

NoteNovember 14, 20232 min read

A unique index on email, and a soft delete that leaves the row in place, which means an address can never be reused. The generated column is null…

The unique index we could not add, and the duplicates we had to find first

Deep diveNovember 7, 202310 min read

A constraint that should have existed since 2016, and 1,240 rows that violate it. Support merging customer records by hand, monthly.

A JSON column that had become a schema

Deep diveSeptember 20, 20239 min read

A settings column with forty keys, four of which are queried, and a WHERE on a JSON path scanning two million rows.

Partitioning by month, and the query that scanned all of them

Deep diveJune 27, 202310 min read

A 400 million row events table and a delete that could not finish. Dropping a partition takes two seconds; getting there takes a schema change.

The covering index that removed a filesort

NoteJune 7, 20231 min read

An index containing every column the query touches means the table is never read, and the plan says Using index. The column order is the design: equality predicates…

Descending indexes, which finally do something

NoteMay 25, 20232 min read

The DESC keyword in an index definition was parsed and ignored until 8.0, so mixed-direction sorts always needed a filesort. This only matters for mixed directions — a…

A mapping that had been dynamic for three years

NoteMay 24, 20232 min read

Dynamic mapping infers a type from the first document it sees, which means the schema was decided in 2020 by whichever product was indexed first. The field that…

Multi-valued indexes on a JSON array, and the CAST

NoteMarch 27, 20232 min read

Filtering on a value inside a JSON array was a full scan until 8.0.17 added multi-valued indexes, and the syntax is unforgiving. The ARRAY keyword in the cast…

A read replica, and the six queries that could not use it

Deep diveMarch 16, 202314 min read

A primary at 80% CPU and reports running during the working day. Setting up the replica takes an afternoon; the consistency model takes a month.