Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
aiken
Aged Yak Warrior
525 Posts |
 Posted - 2005-10-18 : 03:26:17
|
Ok, indexes are pretty straightforard. Clustered, nonclustered, whatever.However, am I correct in thinking that any index on a column or columns is obviated if there's another index on the same column(s), in the same order, with the same sort, with additional columns also included?That is, if I've got a tablecreate table t (a int,b int, c int, d int) , is it pointless (ignoring primary keys for now) to have an index on "a" if there's also an index on "a,b"? And, similarly, if there's an index on "a,b,c", does "a,b" become redundant?I realize that there are primary keys, unique constraints, etc, but strictly for the purposes of query optimization, am I right about that stuff? I've got an app here that's got a whole bunch of what seem like redundant indexes, but I'm scared to go killing them off lest my understanding turn out to be wrong.Thanks-b |
|
|
Kristen
Test
22859 Posts |
Posted - 2005-10-18 : 04:00:29
|
Yeah, that's about right. But also if you have a PK "a, b, c" then its pointless having an index "a, b" as well.Killing off redundant indexes should improve INSERT and UPDATE time. And disk space. Might also improve SELECT time if the optimiser is having to try all the indexes and saying "Nope, that ones no better than the first one I tried"  Kristen |
 |
|
|
aiken
Aged Yak Warrior
525 Posts |
Posted - 2005-10-18 : 04:04:18
|
| Thanks, Kristen. One quick followup: if I have a PK on "a", is it better to change it to be "a,b", even if A is unique and B isn't, or to just create a nonclustered on "a,b"? I realize that it will vary by usage, but is there a general rule of thumb there?Thanks!-b |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-10-18 : 04:13:27
|
| I'm sure there is a stock-answer to that, but I don't know what it is.I always use the shortest possible unique column-list for a PK - most of the time that's only one column.I guess you know that an index based on "a, b" covers that query? SoSELECT AFROM MyTableWHERE B BETWEEN 10 AND 20is "covered" - all the data needed is in the index, so the data itself will not need to be processed. Very fast!Kristen |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-10-18 : 04:15:10
|
| Also ...Indexes for "A, B, C" and another one for "A, C, B" are not interchangeable from the optimisers point of view - so you might NOT be able to drop one of those - it depends on the query ... if there WHERE only uses "A" then either will do - the B and C columns will "cover" the SELECT.Kristen |
|
|
|
aiken
Aged Yak Warrior
525 Posts |
Posted - 2005-10-18 : 04:21:49
|
Gotcha on the importance of column order; that makes perfect sense.I'm going to totally speculate here and say that it seems like changing a PK to include additional columns in the interest of killing off duplicate nonclustered indexes would make sense in proportion to the frequency of inserts/updates versus selects (and joins against) the table, since the larger PK can't be good for selects. So I'm not going to touch that for now. I just noticed that the app literally had several tables with indexes on "a,b", "a,b,c", and "a,b,c,d" on tables with a PK on "a." I've killed off the redundant ones, and so far my phone isn't ringing  .Thanks for your help!-b |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-10-18 : 06:05:07
|
| I reckon that PK on "a" and index on "a,b,c,d" is the answer there.However, maybe there are no queries needing "d", so that could be trimmed to "a,b,c" ...The Index Wizard might tell you the answer??Kristen |
|
|
|
aiken
Aged Yak Warrior
525 Posts |
Posted - 2005-10-18 : 13:51:35
|
| I think the index wizard is my problem here, actually . The app I manage has grown over the years, and I expect that there was a time when queries only needed a,b. Then the index tuning wizard found that some new part of the app benefitted from a,b,c. Then some yet later one improved with a,b,c,d. My fault, really, for not taking notes and deleting the previous ones when a new one was suggested. It woudln't be practical for me to capture a full workload for the entire app and allow query analyzer to delete old indexes, so they've just kind of piled up over time.It would be nice, though, if the ITW had an option to "delete indexes made redundant by newly added indexes." In fact, it seems like it should be possible to write a query to find indexes like that; maybe I'll tackle that just for fun.Cheers, and thanks much for the discussion!-b |
|
|
|
Arnold Fribble
Yak-finder General
1961 Posts |
Posted - 2005-10-19 : 04:05:03
|
| Side note: If you're creating covering indexes, SQL Server 2005 has a new feature that allows extra, non-key columns to be included in an index without eating into the 900 byte index key size limit or the 16 key column limit.http://msdn2.microsoft.com/en-us/library/ms190806 |
|
|
|
|
|
|
|
|
Understanding indexes better