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

The N+1 in the ORM you cannot see from the template

Deep diveOctober 17, 201910 min read

Subquery selects in 6.0, and the aggregate that used to be a loop. Eager loading is all-or-nothing per relation, which is the problem.

A generated column you can index

NoteMay 14, 20192 min read

Indexing an expression is not possible directly, so a query filtering on YEAR(placed_at) or on a JSON path scans the table regardless of what indexes exist. The optimiser…

performance_schema knows which indexes are never read

NoteApril 17, 20191 min read

Guessing which of forty indexes on a table are dead is how they stay for years; the server has been counting reads per index the whole time. Counters…

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.

Make an index invisible to test a drop

SnippetMarch 27, 20191 min read

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…

Two indexes where one is a prefix of the other

NoteJanuary 15, 20191 min read

An index on (a) is redundant when an index on (a, b) exists, because the composite serves every query the single-column one could — and both are being…

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…

A covering index turns two reads into one

NoteAugust 16, 20182 min read

An index lookup finds the primary key and then reads the row from the clustered index to get the other columns, which is a second read per row…