indexing – Page 11 – Türker Yıldırım

Indexing a report query that reads the whole orders table

Deep diveMarch 7, 20139 min read

A daily report scanning 1.2 million rows to return two hundred. Why neither obvious index helped, what the composite one costs, and the two answers I turned down.

An index is unused if the query wraps the column in a function

NoteNovember 20, 20122 min read

An index holds the column’s values, in order. YEAR(created_at) is not one of those values, so there is nothing for the optimiser to look up — it has…

Find tables without a primary key in MySQL

SnippetJuly 3, 20121 min read

EXPLAIN’s rows column is an estimate, not a count

NoteMarch 28, 20122 min read

The rows column in an EXPLAIN is read as a measurement — “this query looked at 8,412 rows”. It is the optimiser’s guess, derived from index statistics, and…

Show the indexes on a MySQL table

SnippetNovember 17, 20111 min read

Moving a latin1 database to utf8 without losing the accents

Deep diveOctober 20, 20119 min read

Two pages read the same column and only one of them was right. Dumping, rewriting and reloading 52,000 rows without turning every accent into a question mark.

A LIKE with a leading wildcard cannot use the index

NoteJanuary 12, 20111 min read

An index on a string column is a B-tree ordered by the leading characters, so MySQL can find a range only when it knows where the range starts.…