Differential D10592
Ignore keys with trailing index on table primary key for now Authored by epriestley on Sep 27 2014, 11:18 PM. Tags None Referenced Files
Subscribers
Details
Ref T1191. We have several keys on <x, y, id>. When id is an auto-increment primary key, I believe this is exactly equivalent to a key on <x, y>, because the leaf nodes are implicitly sorted by id. We omit the implicit id elsewhere. It would be nice to drop the id bit for consistency, but it's not doing any harm and this doesn't need to block the primary work of T1191. Saw slightly fewer warnings.
Diff Detail
Event TimelineComment Actions I think the idea was for the key to be guaranteed unique by the inclusion of the id? Seems silly in practice if you know id already. Comment Actions AFAIK, there's no need or value to making keys unique unless you want to guarantee that you can't have two rows with the same value. My understanding (which may not be totally accurate) is that the table's primary key is sort of implicitly appended to any keys you define, so an <x> key on a normal table is really an <x, (id)> key. Defining id explicitly just makes it an <x, id, (id)> key, which is redundant. In particular, MySQL serves the queries (with key <x>):
...with the same query plan according to EXPLAIN, which suggests the key has id in it implicitly at the leaf nodes. I think this generally makes sense: when MySQL is building the tree for the index, it has to have some way to compare nodes with the same x value, and using id seems like the most natural thing to do. I think there's some complexity if the table has an unusual primary key, but in cases where everything is "normal", I believe adding <..., id> to the end of a non-unique key never affects anything (and adding it to the end of a unique key is pointless, since it removes the value of the "unique" constraint). This is pretty fluff in any case and AFAIK there's no negative effect to it, it's just slightly inconsistent. |