Page MenuHomePhabricator

Ignore keys with trailing index on table primary key for now
ClosedPublic

Authored by epriestley on Sep 27 2014, 11:18 PM.
Tags
None
Referenced Files
Unknown Object (File)
Fri, Mar 22, 8:57 AM
Unknown Object (File)
Tue, Mar 5, 2:03 PM
Unknown Object (File)
Feb 14 2024, 3:22 PM
Unknown Object (File)
Feb 14 2024, 3:22 PM
Unknown Object (File)
Feb 14 2024, 3:22 PM
Unknown Object (File)
Feb 13 2024, 4:08 AM
Unknown Object (File)
Feb 7 2024, 8:54 PM
Unknown Object (File)
Jan 29 2024, 7:33 PM
Subscribers

Details

Summary

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.

Test Plan

Saw slightly fewer warnings.

Diff Detail

Repository
rP Phabricator
Lint
Lint Not Applicable
Unit
Tests Not Applicable

Event Timeline

epriestley retitled this revision from to Ignore keys with trailing index on table primary key for now.
epriestley updated this object.
epriestley edited the test plan for this revision. (Show Details)
epriestley added a reviewer: btrahan.
btrahan edited edge metadata.

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.

This revision is now accepted and ready to land.Sep 30 2014, 4:53 PM

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>):

  • SELECT x FROM table WHERE x = <something>
  • SELECT x FROM table WHERE x = <something> ORDER BY ID

...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.

This revision was automatically updated to reflect the committed changes.