Menu
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?
718
Indexes should not be used on tables containing few records. Tables that have frequent, large batch updates or insert operations. Indexes should not be used on columns that contain a high number of NULL values. Indexes should not be used on the columns that are frequently manipulated.
Indexes are also used to aid in filtering tables to assist in optimizing queries. The most obvious case of this is to optimize WHERE clauses. For example, the query "select * from employee where lastname = 'Jones'" results in a live cursor.
The reason the non-clustered index is not used is because it is more efficient to select the single row using the unique primary key clustered index. You can't get any faster than that to select all columns for a single row (barring a hash index on an in-memory table). Save this answer.
The SQL NOT condition (sometimes called the NOT Operator) is used to negate a condition in the WHERE clause of a SELECT, INSERT, UPDATE, or DELETE statement.
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
171
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)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
