Adding a index on my table for this query

Question

This table gets hit with this query the most, so I want add a index to speed things up, this table will have 5 million rows it in.

My query looks like this:

SELECT someID FROM someTable WHERE myVarChar = @myVarChar AND MyBit = 0 AND MyBit2 = 1 AND MyBit3 = 0

myVarChar is unique also.

What would the best index be for this type of query?

I currently have a single index that covers all of the 4 columns in the above query.

I am using sql server 2008 standard.

Do I have to reindex every so often or its automatic?

Answer 1

If you have a covered index with the four columns that's probably the best it's going to get. You should reindex and update statistics on a regular basis, though - weekly at least.

Answer 2

Is someID the primary key of that table? If not, you should add it to your index to prevent a bookmark lookup.

Also, look at the execution plan for that query, if your index is properly constructed, you should see two icons in the execution plan view: SELECT and an Index Seek.

Answer 3

I never timed the difference myself, so please remember that this "answer" is more a suggestion that may be worthwhile to try it and measure results...

A senior SQL-Server guy I worked with a couple years ago insisted that we use indexed tinyint fields rather than indexed bit fields. I think he said the performance gain had something to do with the way SQL-Server's optimizer worked.

Note that we were working on SQL-Server 2000 as well, so things may have changed for subsequent releases of the dbms.

Answer 4

This would probably be the best index for your query, but after you create it, check the execution plan to make sure it's being used. If you get two different execution plans when you run the raw SQL in query analyzer, and when you run it as a stored proc, read about parameter sniffing.

create unique index IX_MyIndex on SomeTable (myVarChar, MyBit, MyBit2, MyBit3) include (SomeID)

Check your execution plan to make sure you're getting an index seek with no key lookup. If you do see a key lookup, you may need to change or add columns to your index, or to the include () part. Also, if you're going to do any sorting - especially desc, make sure to add that to your index.