Last modified: 2011-03-02 00:47:33 UTC
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.
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; -- Insert 100 records into foo, for (0,0) - (9,9) 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 |
Fixed in r45821.
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.
