How to build an index that is best for this SQL?

I’m querying database by a simple SQL like:

SELECT DISTINCT label FROM Document WHERE folderId=123

I need a best index for it.

There are two way to implement this.

(1) create two indexes

CREATE INDEX .. ON Document (label)
CREATE INDEX .. ON Document (folderId)

(2) create a composite index:

CREATE INDEX .. ON Document (folderId, label)

According to the implementation of the database index. Which method is more reasonable? Thank you.

Answer

Your second index — the composite index — is the best index for this query:

SELECT DISTINCT label
FROM Document
WHERE folderId = 123;

First, the index covers the query so the data pages do not need to be accessed (in most databases).

The index works because the engine can seek to the records with the identified folderId. The information on label can be pulled. Most databases should use the index for pulling the distinct labels as well.