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

Transactions per test are faster than truncation

NoteSeptember 19, 20182 min read

Resetting the database between tests by truncating every table costs a statement per table per test, which on forty tables and four hundred tests is sixteen thousand statements.…

A CTE is not a temporary table, and the plan says so

NoteSeptember 13, 20182 min read

A common table expression reads like a named intermediate result, and in MySQL 8.0 it may be merged into the outer query or materialised into a temporary table…

EXPLAIN ANALYZE tells you what happened, not what might

NoteSeptember 8, 20181 min read

EXPLAIN shows the plan the optimiser chose and its estimates, and the estimates are frequently wrong — a query planned for 40 rows that reads 400,000 looks fine…

Deleting a user is harder than creating one

Deep diveAugust 16, 20189 min read

The delete request arrived and the row was the easy part. Foreign keys model structure, not ownership — and the person is in six other places.

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…

A recursive CTE walks a tree without a loop in PHP

NoteJune 27, 20182 min read

Fetching a category and all its descendants meant a query per level, in a loop, with the depth unknown — so the number of round trips depended on…

The N+1 that does not appear in the query log

Deep diveJune 14, 201810 min read

340 queries on a page showing twenty rows, and none of them are slow. Every one is under the threshold, which is why the slow log has nothing…

A window frame defaults to something you did not intend

NoteJune 7, 20182 min read

A window with ORDER BY and no frame clause defaults to everything from the start of the partition to the current row, so SUM gives a running total…

An integration test needs a real database or it proves nothing

NoteJune 5, 20182 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…

posts_per_page = -1 is a production incident waiting

NoteMay 30, 20182 min read

It means unlimited, and it is fine on a development site with forty posts. The site it eventually runs on has forty thousand. fields => ‘ids’ is the…