The pl_from (pl_from, pl_namespace, pl_title), il_from (il_from, il_to) and tl_from (tl_from, tl_namespace, tl_title) indices are UNIQUE, while the pl_namespace (pl_namespace, pl_title, pl_from), il_to (il_to, il_from) and tl_namespace (tl_namespace, tl_title, tl_from) indices aren't. They contain the same fields, though, so if one is UNIQUE the other one should be UNIQUE too.
Version: 1.14.x
Severity: normal
| Status | Subtype | Assigned | Task | ||
|---|---|---|---|---|---|
| Open | Feature | None | T18660 Database table cleanup (tracking) | ||
| Resolved | tstarling | T18012 Schema change rotation script | |||
| Resolved | None | T18645 Inconsistent indices on pagelinks, imagelinks, templatelinks tables |
Only one constraint is necessary. Would there be any benefit to applying the constraint to the other permutations?
Well of course there's no added value in terms of constraints, but it could help MySQL optimize stuff, I guess (that really is just a guess, I have no idea).
It could make the difference between MySQL using "const" or "eq_ref" instead of "ref" when planning the query. MySQL doesn't seem to be smart enough to realize the non-UNIQUE key is still going to be unique.
I ran the following test:
create table foo (i integer not null, j integer not null, unique key key1 (i,j), key key2 (i,j)) engine=InnoDB;
explain select * from foo force index (key1) where i=4 and j=2;
| 1 | SIMPLE | foo | const | key1 | key1 | 8 | const,const | 1 | Using index | |
explain select * from foo force index (key2) where i=4 and j=2;
| 1 | SIMPLE | foo | ref | key2 | key2 | 8 | const,const | 1 | Using index |
Adding UNIQUE fields does not speed MySQL up, it slows it down. This is documented in the MySQL manual: removing unique constraints is suggested as a means of speeding up INSERTs.
The "speeding up" discussed above regards SELECT performance, not INSERT performance. Does adding uniqueness constraints really not speed up SELECTs in MySQL?
(In reply to comment #6)
The "speeding up" discussed above regards SELECT performance, not INSERT
performance. Does adding uniqueness constraints really not speed up SELECTs in
MySQL?
I guess someone (me?) would have to see whether we actually have any SELECTs in the code that would benefit from this additional UNIQUE index.