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

This shocked me a bit because I’ve never run into a problem with the evaluation of the boolean operators. I suspect this is because I make liberal use of parenthesis when I’m writing out SQL statements.  However boolean operators are evaluated in the following order : NOT, AND, OR.  As I said it’s not a huge problem to deal with, a few parenthesis will fix any issues that you might encounter, but it could create some interesting problems if you aren’t aware of it and the parenthesis keys on your keyboard is broken.