Does it make sense to create a separate index against a column, that is also a part of the composite primary key?

Question

I am using MySQL as my RDBMS.

But I think it must be applicable to other relational DBs.

I have a table Z, where I have 5 columns: a, b, c, d, e.

Columns a, b, c comprise a composite primary key.

Now, when it comes down to querying in the WHERE clause there will be times when I will be fetching data based on the values of columns a, b, c. But only one column out of 3 will be set.

Do I need to create 3 indices against these columns?

Follow-up question: what if I need to query my table knowing values for 2 columns out of 3? Will the creation of an additional 3 indices help to speed up my queries? (a, b) (a, c) (b, c)

Please advise.

Answer 1

...will be fetching data based on the values of columns a, b, c. But only one column out of 3 will be set.

If that's the case you'll need three indexes:

• If a is set your primary key index (a, b, c) will suffice. You don't need to create an extra index for this case.
• If b is set you'll need the index (b) for this query to be fast.
• If c is set you'll need the index (c) for this query to be fast.

The index (a, b, c) is not useful when a is null. Remember, null is not a value.