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

A histogram on a skewed column, and the plan it fixed

NoteJanuary 9, 20241 min read

A status column where one value covers ninety-four per cent of rows, and an optimiser assuming an even distribution. Histograms are for columns that are skewed and not…

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…

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.

The read model we built before anybody asked

NoteOctober 3, 20232 min read

A denormalised projection built for a dashboard that was specified, prioritised and then never scheduled. The projection was the right shape for a requirement that did not arrive,…

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.

An event log that is not an event store

Deep diveAugust 10, 202310 min read

Wanting to know what happened to an order without adopting event sourcing. A support question that takes twenty minutes and three tables.

The N+1 that only appeared in production

Deep diveJuly 29, 202310 min read

An endpoint at 40 ms locally and 2.1 seconds in production. Nine hundred queries on one request, and three on the same request in a test.

MariaDB 11.0 and a new optimizer cost model

NoteJuly 18, 20232 min read

The 11.0 release rewrites the cost constants, which is a change that improves most plans and changes some of yours. A cost model change is the kind of…

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.

A search that should have been a LIKE

NoteJune 20, 20231 min read

A search cluster indexing 4,000 rows to support an admin lookup by name, added because “search” was in the requirement. A prefix match on an indexed column is…