December 14, 2006 at 3:14 pm
When designing a compound (covering) index, we need to consider the order of columns to be indexex. What criteria do we need to follow? Should we follow the same order when using SELECT clause?
Many thanks in advance.
December 14, 2006 at 3:28 pm
It's all about how your application will query it (and/or needs it sorted).
If you frequently select all orders for a given date, probably have a secondary index on order_date.
If you often filter by customer as well, you then logically add the customer to that index (order_date, cust_id)
Then, sometimes status is used, so you make it (order_date, cust_id, order_status)
...
Then you find that some queries only need the order_id returned (i.e. don't need to hit the data page when you want to find orders on a given date that have a shipment this week--shipments being in a separate related table), so you add order_no (the PK) to the index (order_date, cust_id, order_status, order_no)
Great thing about indexes is you can usually always change/add to them without breaking your application.
December 15, 2006 at 4:13 am
If you're always using all of the index fields in your filtering, it's customary to use the field that has less options first.
i.e if I have a table with LoginLogout table with a TYPE column (Options: Login, Logout) and a DATE column (Options: any date) then the TYPE field should come first in the index.
Adi
December 15, 2006 at 4:27 am
The order of the columns is less critical than it used to be, I figure this has changed during service packs of sql2k, it also seems less important in sql 2005.
I still tend to look for the most selective column for the first column of the index however, if the index isn't selective, even as a covered index the optimiser may still ignore it. There's some other factors too but starts to get too complex to explain in full.
You can try creating the index in reverse order too and/or in variations, sometimes even with other columns ( which might be useful for another query ) and see which the optimiser decides to use.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply