query-optimization – Page 12 – Türker Yıldırım

SQL_CALC_FOUND_ROWS is usually slower than a second COUNT

NoteSeptember 25, 20151 min read

SQL_CALC_FOUND_ROWS promises the total row count for free alongside a LIMITed page, in one query. The catch is that it forces MySQL to evaluate every matching row rather…

A covering index answers the query without touching rows

NoteSeptember 24, 20151 min read

An index lookup normally finds a primary key and then goes to the table to fetch the columns you asked for — a second read per row. If…

WP_Query fields => ids when you only need the IDs

NoteSeptember 8, 20151 min read

A normal WP_Query hydrates every result into a WP_Post object and primes the meta and term caches for all of them. When the only thing you want is…

EXPLAIN type ALL means it read the whole table

NoteMarch 31, 20151 min read

EXPLAIN output has a dozen columns and two of them carry most of the signal. type describes how MySQL reached the rows, and ALL is the worst answer:…

Show the largest tables in a MySQL database

SnippetMarch 3, 20151 min read

The leftmost prefix rule decides if your index is used

NoteFebruary 12, 20152 min read

A composite index on (a, b, c) is usable by a query filtering on a, on a and b, or on all three. It is not usable by…

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.

Show the size of every index on a table

SnippetJune 3, 20141 min read