For example, this is a sample table

id int PK,
pid int,
cid int,
deleted int,
createdAt timestamp,
UNIQUE KEY pid_cid(pid, cid)

used WHERE clauses are

where pid=$pid and cid=$cid [and deleted = 1];
where pid=$pid [and deleted = 1];
where cid=$pid [and deleted = 1];

since I have indexed UNIQUE KEY pid_cid(pid, cid), should I index on pid and cid separately?

Or selecting by a index pid faster than selecting by a UNIQUE KEY pid_cid(pid, cid)?

up vote 4 down vote accepted

No. Two separate index is not the same as a composite. Although MySQL now has index merge other than analytical queries it's not very efficient.

In your case the best to have the unique key and a single column index.

Start the composite with the higher cardinality column. If pid has more distinct values than it's good as it is. If cid has more than I would suggest to use UNIQUE KEY (cid, pid). Also create a separate index which is only the column that is the second in the unique key.

For example

UNIQUE KEY (cid, pid), KEY (pid) or UNIQUE KEY (pid, cid), KEY (cid)

This way:

  • where pid=$pid and cid=$cid [and deleted = 1] will use the UNIQUE KEY

One of these will use the composite unique key the other will use the single column index.

  • where pid=$pid [and deleted = 1];
  • where cid=$pid [and deleted = 1];

 Alternative solution

You can make the composite index the PRIMARY KEY and have a single key on the other column:

Pros:

  • Very fast lookup on PRIMARY KEY
  • No need for an extra ID
  • Rows will naturally be clustered around the first column of the PK:

Since you have queries where you only have one of the column cid = X or pid = X these rows will be close(r) to each other so InnoDB needs to read less pages. Which can significantly speed up multiple rows resulting queries.

Cons:

  • Every index will have the PK concatenated to it so if you have a lot of secondary indexes this can consume some space.
  • If neither pid or cid are in incrementing order your INSERT throughput will suffer
  • In this case: UNIQUE KEY (cid, pid), KEY (pid), KEY(cid) is not necessary? – Sato Aug 22 '17 at 23:32
  • No, KEY(cid) is covered by the unique key. Fields are kind of concatenated (for lack of a better word) in the index and prefix search is possible. – Károly Nagy Aug 23 '17 at 13:29

No. But I will say something different than Károly Nagy.

UNIQUE and 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 pid.

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 .

A 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 deleted and 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.

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.