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

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

Elasticsearch 1.0 aggregations replaced facets

NoteMay 15, 20143 min read

Facets answered exactly one question — how many documents per term, per range, per date interval — and could not be nested, so a sidebar showing brands within…

The 5.6 ALTERs that still copy the whole table

NoteJanuary 29, 20142 min read

5.6’s online DDL gets summarised as “ALTER TABLE no longer locks”, and adding an index really is online now. Several other alterations are not, they are the ones…

MongoDB indexes are not free just because the schema is

NoteSeptember 30, 20132 min read

The pitch for a document store is that you can add a field without a migration, and that part is true. It gets read as “no schema, no…

Using temporary and Using filesort are one problem, not two

NoteAugust 20, 20132 min read

Seeing both in the Extra column of an EXPLAIN looks like two separate faults to chase. They are one: the GROUP BY built a temporary table of the…

Add an index to a large MySQL table

SnippetJune 12, 20131 min read

MySQL 5.6 adds an index without copying the table

NoteMay 8, 20132 min read

Adding an index to a large InnoDB table on 5.5 rebuilds it into a copy and blocks writes for the whole operation, which is why the index that…