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

A generated column indexes a field inside JSON

NoteFebruary 4, 20161 min read

A JSON column cannot be indexed directly, so a query filtering on something inside it scans the table. A generated column extracts the value into a real column…

Convert a MySQL database to utf8mb4

SnippetNovember 17, 20151 min read

Reading EXPLAIN until a slow catalogue query gets fast

Deep diveOctober 30, 20158 min read

4.2 seconds on 60,000 rows. Following EXPLAIN from a full table scan to a covering index, and why the composite index that existed was never used.

ON DUPLICATE KEY UPDATE is one round trip, not two

NoteJune 30, 20151 min read

The usual upsert is a SELECT to see whether the row exists and then an INSERT or an UPDATE. That is two round trips, and between them another…

INSERT IGNORE swallows more errors than the duplicate you meant

NoteJune 9, 20151 min read

INSERT IGNORE is reached for to skip rows that already exist. What it actually does is downgrade every error in the statement to a warning, which is a…

Kill all sleeping MySQL connections

SnippetApril 7, 20151 min read

Show the largest tables in a MySQL database

SnippetMarch 3, 20151 min read

Find duplicate rows in a MySQL table

SnippetJanuary 6, 20151 min read

Find rows in one table with no match in another

SnippetDecember 11, 20141 min read

Altering a 30-million-row table without downtime

Deep diveOctober 23, 20149 min read

ALTER TABLE on 30 million rows blocks writes for 40 minutes, and checkout writes to that table. A shadow table, triggers, and a backfill paced by replication lag.