Indexing tip

Searching by more than one column (i.e. more than one predicate in the where clause) is a very common scenario. To fasten such queries, creating indices is a common practice. The question: How to reuse the same index for both single column and multi column searches? For example, consider a student table with id, first_name and last_name columns. If the DB receives numerous queries with first_name and last_name in there where clause, then what should the indexing strategy be?

 

To elaborate on the question, the indexing strategy needs to support the following queries

 

Select * from student where first_name like ‘blah%’; -- Scenario 1

Select * from student where last_name like ‘blah%’; -- Scenario 2

Select * from student where first_name like ‘blah%’ and last_name like ‘blah%’; -- Scenario 3

 

One possible solution is to create 3 indices for the 3 scenarios (one for first_name, one for last_name and one for first_name and last_name). This is not so interesting.

 

The other alternative leverages on the fact that a query based on first_name only will also use an multi-column index (index on more than one column) provided first_name is the first column in the multi-column index.

 

So in the above scenario, the following indices need to be created:

 

CREATE INDEX idx_student_firstName_last_name ON student (first_name, last_name); -- takes care of first_name only (scenario 1) and first_name and last_name queries (scenario 3)

CREATE INDEX idx_student_last_name ON student(last_name); -- takes care of last_name queries (scenario 2)