sql – Page 6 – Türker Yıldırım

A report that was a loop, and the window function it became

Deep diveAugust 19, 202010 min read

380 MB of memory and a nightly job that could not be run on demand. Running totals and rankings needed a loop before window functions, and the loop…

Measure replication lag with a heartbeat table

SnippetJuly 24, 20201 min read

SKIP LOCKED, and the queue table that scales

NoteJuly 16, 20202 min read

Two workers claiming with FOR UPDATE serialise, so adding workers adds no throughput — which is the thing a queue exists to provide. Keeping the transaction short is…

utf8mb4_0900_ai_ci sorts differently from what you had

NoteFebruary 12, 20201 min read

8.0 changed the default collation, and a table created after an upgrade sorts differently from one converted before it — silently, and in a way that only shows…

A recursive CTE needs a depth guard

NoteFebruary 6, 20202 min read

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

utf8 is three bytes and utf8mb4 is the one you meant

NoteDecember 17, 20192 min read

The character set MySQL calls utf8 stores at most three bytes per character, which excludes emoji and several CJK ranges — and the failure is a truncated string…

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.

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

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…