CODEWITHSUNDEEP
sqldatabaseindexing
zzz sleeper
zzz sleeper

Reputation: 55

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.

Upvotes: 1

Views: 84

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269593

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.

Upvotes: 1

Conffusion
Conffusion

Reputation: 4465

The answer depends also on the used dbms. I'm not sure if all dbms support the use of multiple indexes.

I should think the combined index (folderid, label) is the best solution in your case as it is possible to build your result set solely by using index data. So it doesn't even need to access the real data. It can even be a strategy to add extra columns to an index so a query which is called a lot can be answered by only accessing the index.

Upvotes: 1

Related Questions