No. But I will say something different than Károly Nagy.
PRIMARY KEY not only index the column or columns indicated, they provide a uniqueness constraint.
UNIQUE(cid) only says that
cid shall not be duplicated.
UNIQUE(cid, pid) says that the pair of values shall not be duplicated in the table. That is,
cid may be duplicated, but only with different values of
Let's back off of uniqueness for a moment.
WHERE pid=123 -- INDEX(pid) works well;
so does INDEX(pid, cid) -- in this order
WHERE pid=123 AND cid=456 -- INDEX(pid, cid) or INDEX(cid, pid) works best
That is, the order of columns in the
INDEX matters, but the order in of clauses in the
WHERE does not. More and more .
Your sample table looks like a many-to-many mapping table. If so, I advise that you follow the rest of the tips here .
PRIMARY KEY, is appropriate, is faster than a
UNIQUE key since it is "clustered" with the data.
Using a second index as
INDEX(cid, pid), even though
INDEX(cid) is just as good and the pair is unique is better because it is "covering" (unless you need to check
deleted) and it avoids unnecessarily checking uniqueness during inserts.
I suggest that you will eventually decide that
createdAt are not worth the bother.
For the 6 different
WHERE clauses you mention, I suggest this set:
PRIMARY KEY(pid, cid) -- do not include `deleted`; this declares the pair unique
INDEX(cid, pid, deleted)
The perfect list would include about 4 indexes, but I don't think it is worth it. These two handle even the worst of the 6 cases adequately well.