Database Indexes: tips

Database Indexes are sometime tricky. But it's easy to fall into traps by following simple advices. Here are some common tips (specific to t-sql and it may or may not apply to other RDBMS) listed here:

Sample Database Table:

Customer_Id	Primary Key, Clustered Index, Numeric
Customer_Name	Part of multi-column index on "Customer_Name, Customer_Mobile, Customer_Location", Varchar
Customer_Mobile	Indexed, Varchar
Customer_Location	Part of multi-column index on "Customer_Name, Customer_Location"

1. Index is not used, when columns is in function

Example of Bad:
Select * from customer
where IsNull(Customer_Name,'!true') = IsNull(@CustomerName, '!true')

Good query:
Select * from customer
where Customer_Name= @CustomerName
OR (Customer_Name is null AND @CustomerName is null)

2. On DataType mismatch,

Example of Bad:
Select * from customer
where Customer_Mobile = 9900114477

Select * from customer
where Customer_Id = '2'

Good query:

Select * from customer
where Customer_Mobile = '9900114477'

Select * from customer
where Customer_Id = 2

3. Using Like on Wrong End:

Example of Bad:

Select * from customer
where Customer_Name LIKE '%ram'

Good Option:

If you are gonna use put % always at first, use reverse Indexing

4. : Multi-column Index is created with wrong sequence of columns

Example of Bad: 

Select * from Customer 

Where Customer_Name = 'CN' and Customer_Location = 'CL'

Good Option:

On Index is on columns: 

1. Customer_Name

2. Customer_Mobile 

3. Customer_Location

Which is only used when one of these is in your where clause:

1. Customer_Name

2. Customer_Name and Customer_Mobile 

3. All three, Customer_Name, Customer_Mobile and Customer_Location

So only option is to modify Index Or Add new index on Customer_Location