Index usage

What do you think? Will an index be used in these cases?

1. Table returns 2,500,000 (2.5 million) records from a table having 10,000,000 rows (10 million).

2. You use a value !=’45′ condition in your where clause. ( Value column is indexed)

3. You say Value=’%ABC’ condition in where clause (value column is indexed)

4. You say Value=’ABC%’ condition in where clause (value column is indexed)

5. Where Value IS NULL (value column is indexed)

6. Where Value IS NOT NULL (value column is indexed)

In all these cases, do you think the index will be used or not?

Index will be used only in case 4. Everywhere else, index will not be used!

Reason:

1. Query returns more than 20% of total rows. (its not a definite rule.. but in most cases, it will not use the index if it returns more than approximately 20% of rows)

2. Index will not be used in case of , != operators

3 and 4. ‘ABC%’ will use index, but not the ‘%ABC’ case

5 and 6. NULL doesnt use index