Database Index not used if the where criteria is != ?

Question

I have a index on a column and it is correctly used when the query is

select * from Table where x = 'somestring'

However it seems to be not used when the query is something like

select * from Table where x != 'someotherstring'

Is this normal or am I missing something else in the query? The actual query is of course much larger and so it could be caused by some other factor. Any other ideas why an index would not be used in a query?

Answer 1

That is indeed normal - to use the index, you need to use a exact match (like the "=" equals operator), or something like a range query.

A query that defines a "negative" criteria (NOT something or another) typically can't be satisfied by an index lookup - you'll have to look up everything except a certain value. That doesn't work nicely - typically, a full table scan (clustered index scan in SQL Server) will be quicker, just checking for the criteria to be matched (or not matched, in that case).

Answer 2

You didn't say what database engine you are using.

MS SQL Server, for example, has both Equality indexes and Inequality indexes. The latter are used when the not equal operator is in play.

Answer 3

This is normal. Index will only be used if you have a '=' condition. Searching index for != condition is not effective.

Similarly, this may use the index (in Oracle)

select * from Table where x like 'some%'

but this wouldn't

select * from Table where x like '%thing%'

Also, select * from Table where x between 1 and 10 will use the index

but not select * from Table where x not between 1 and 10

Answer 4

this is absolutely normal. index is used to look for exact something. where you start when I ask you to look a dictionary when I told you not start with 'S'.

you can always do this.

select * from Table a
where not exist (select * from table b where x = 'somestring' and a.key = b.key)

Answer 5

I think that a != condition can use an index (in MSSQL). According the the execution plan in MSSQL, if I have an index on a single field, and I apply a where clause on that field, one with a != and one with =, they both result the same execution plan, both using an index seek.

Answer 6

It may use index if the index is clustering and there are not so many different values of the indexed attribute (so we can quickly decide which blocks we may skip). But if the indexed attribute is, say, a key then using index in this case makes absolutely no sense.