CODEWITHSUNDEEP
sql-serverperformancesql-server-2008indexing
Blankman
Blankman

Reputation: 266960

Adding a index on my table for this query

I have a 5 million row table that gets hit with a specific query the most. So I want add a index to speed things up.

Query:

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

Note, the myVarChar column is unique.

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. Also, do I have to reindex every so often or its automatic?

I am using sql server 2008 standard.

Upvotes: 2

Views: 2475

Answers (4)

robinw
robinw

Reputation: 1

First, you need to use this kind of query to get the parttern of columns, observe the values you get and ,order the columns by discrimition. 

SELECT myVarChar ,count(*)
FROM someTable 
GROUP BY myVarChar 
ORDER BY 2 desc

Second,You could use create index with this dialog:

CREATE UNIQUE INDEX IX_MyIndex 
ON SomeTable (myVarChar, MyBit, MyBit2, MyBit3) 
INCLUDE (SomeID)

What codemonkey said is right, do avoid to build nonclustered index for boolean kind value. but you can try to build the index with ( other kind value + boolean value)

Upvotes: 0

Seibar
Seibar

Reputation: 70243

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.

Upvotes: 0

bobwienholt
bobwienholt

Reputation: 17610

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.

Upvotes: 6

Otávio Décio
Otávio Décio

Reputation: 74250

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.

Upvotes: 2

Related Questions