The idea is to make fast queries based on columns c1, c2, and c3.
There are 3 kinds of queries:
SELECT COUNT(*) FROM tbl WHERE c1=... AND c2=...
SELECT c2, c3 FROM tbl WHERE c1=...
SELECT DISTINCT c2 FROM tbl WHERE c1=... AND c3=...
What is the best way to build index if I want to let (c1, c2) be unique?
I'm thinking of a single index like this:
UNIQUE INDEX idx_c1_c2 (c1, c2)
But the 3rd query wouldn't be using this index.
Any suggestions?
WHERE c1= AND c2= needs INDEX(c1, c2) in either order. If that pair is "unique", then make it UNIQUE instead of INDEX. Or consider making it be the PRIMARY KEY (which is also UNIQUE and an INDEX).
WHERE c1= will happily use any kind of index starting with c1.
WHERE c1= AND c3= would use only the c1 part of (c1, c2), which would be somewhat useful. Better would be to add another index: INDEX(c1, c3) in either order. Suggest (c3, c1) to get some variety.
No single index will work "well" for all three queries.
Searching by PRIMARY KEY is usually faster than by UNIQUE INDEX if the table is Engine=InnoDB. If you have an AUTO_INCREMENT as the PRIMARY KEY now, consider whether it is worth keeping, versus replacing by the "natural" (c1, c2).
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments