Is two separate index equals to a composite index?

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)?

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

StackExchange Database Administrators Q#184036, answer score: 5