Comment 1 for bug 1130498

This affects pt-osc and ptc currently because these tools use the NibbleIterator module. I discovered this problem too awhile ago, but haven't gotten to fixing it because it's a weird and complex problem, and nibbling/chunking is not easy to change in the tools.

Three things are happening: first, NibbleIterator does make sub-optimal WHERE clauses with multi-column indexes. With 3+ column indexes, the tool makes a WHERE clause with mostly superfluous predicates. Second, the MySQL query planner/optimizer should cancel out these superfluous parts, but instead they confuse MySQL and make it use only part of the index. Using part of the index is where the "MySQL used only 8 bytes of the PRIMARY index instead of 16" comes from. We added --[no]check-plan this to detect when this happen in the wild because--third thing--this doesn't happen all the time. Sometimes MySQL isn't confused, and sometimes it is. When it is and uses part of the index, the result is an index scan. That's not too bad unless the table is large (e.g. millions of rows), and then even an index scan become expensive; and that's why --no-check-plan results in "Rows are copying very slowly".

So yes, we need to fix NibbleIterator, but I don't see this happening for awhile. I would like it to happen for PT 3.0, tentatively schedule for this summer, because we plan to rewrite pt-table-sync to use NibbleIterator like pt-table-checksum.

So the only workaround is currently to use a simpler index, i.e. 1 or 2 columns.