mysql – Page 23 – Türker Yıldırım

An integration test needs the real database or it proves nothing

NoteNovember 19, 20192 min read

A repository test against a mocked connection asserts that the code calls the methods you expected, which is a restatement of the code rather than a check on…

Autoloaded options are read on every request, all of them

NoteNovember 19, 20192 min read

Every option with autoload set to yes is fetched in a single query on every page load, and a plugin storing a megabyte there makes every request slower…

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.

Writing the row and the event in the same transaction

NoteNovember 7, 20192 min read

Saving a record and publishing an event are two systems and cannot be made atomic, so either the row exists with no event or the event was sent…

Find indexes that have never been read

SnippetNovember 5, 20191 min read

SKIP LOCKED, and the queue table that finally scales

NoteOctober 31, 20192 min read

Two workers claiming with SELECT … FOR UPDATE serialise — the second blocks on the row the first holds — so adding workers adds no throughput at all.…

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 recursive CTE needs a depth guard, not just a limit

NoteOctober 27, 20192 min read

A cycle in the data — a category set as its own ancestor through the admin — recurses until cte_max_recursion_depth stops it with an error, and the default…

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.

Transactions per test beat truncation, until a second connection

NoteOctober 10, 20192 min read

Truncating every table between tests costs a statement per table per test, which on forty tables and four hundred tests is sixteen thousand statements; a transaction and a…