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

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,…

A subquery select instead of a relationship you read once

NoteNovember 28, 20192 min read

Eager loading an entire relation to display one field from the most recent row is a hydration of every row in that relation, per parent. The result is…

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.

EXPLAIN ANALYZE shows what happened, not what might

NoteOctober 29, 20191 min read

EXPLAIN reports the plan and the optimiser’s estimates, and the estimates are frequently wrong — a query planned for 40 rows that reads 400,000 looks entirely healthy. The…

A test that asserts on the query count

NoteOctober 28, 20192 min read

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

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.

Lazy collections and the report that would not fit in memory

Deep diveJune 24, 201910 min read

A memory limit hit on the fourth of the month, every month. A collection is an array, and an array is all of it at once — until…

Querying orders through WooCommerce rather than through posts

NoteJune 6, 20192 min read

An order is a post today and the CRUD layer exists so that it does not have to be forever — code that queries wp_posts directly is code…

A window function cannot be filtered without a derived table

NoteMay 28, 20192 min read

Window functions are computed after WHERE and GROUP BY, so a predicate on the result has nowhere to go in the same query block. Putting the restriction inside…