Joel Hainley

Hopefully that title got your attention. When I started reading about some of the things that can cause the query optimizer to ignore your indexes it really caught my attention. Below are a few seemingly innocuous things that you can do in your sql statements to force the query optimizer to happily ignore your indexes.

• Use a NOT operator in your WHERE clause – That’s right, if you don’t want to use indexes just drop a NOT operator in your WHERE clause
• When Using An OR operator, reference columns that are not indexed – If you specify columns in an OR operator that are not indexed, all indexes that are associated with columns in the or clause will be ignored.
• Put a leading wildcard character in your WHERE clause – If you do something along the lines of “select NAME from school_records WHERE name is like %XX%”, the index that you put in to speed up searches of the names will do absolutely no good.

There will probably be MANY more things that I will discover as time goes along related to nuances of indexes but I thought these were interesting because they seem really harmless. I think it’s useful to know that how you go about logically selecting data can impact query performance in potentially drastic ways