July 16, 2007 at 1:32 pm
I have the following confusion at hand:
I have a huge table named product.
I have the two different indexes:
index1: index keys =siteId, Catid (non-unique, non-clustered)
index2: index keys =siteid, Catid, date (non-unique, non-clustered)
Problem:
Do I really need these two separate indexes?? or is index2 enough
July 16, 2007 at 1:42 pm
If you are doing searches and retrievals that use all the fields in index 2 then stick with index 2.
Remember an index will be ignored if the query optimiser thinks it isn't selective enough but let us suppose that your index is highly selective.
A search on SiteID will use index 2.
A search on SiteID and CatID will use index 2
A search on SiteID, CatID and Date will use index 2. I wouldn't call the field Date).
A search on CatID and/or Date won't use the index. You will either fall back to a Clustered Index Scan or a table Scan.
If you are going to do a search on just CatID, or CatID and Date then having an index on CatID and Date may be worth while.
You may want to consider what you use for a clustered index. you may benefit from having the clustered index on something other than the primary key.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply