November 8, 2007 at 2:37 pm
Can someone give me an idea of what the difference is between a traditional covering index and an index that covers using the includes clause?
November 8, 2007 at 4:27 pm
From what I've been seeing, the difference between putting it in the main index field list versus including it is in how you're using the fields in the query you're optimizing.
A few pointers (rules of thumb right now) on covering indexes:
- if a field is ONLY used in the select part should be thrown into the INCLUDE part
- fields that are in the ON, WHERE, ORDER BY and GROUP BY should likely end up in the "main" part of the fields. which order will be important and I don't necessarily have a good rule for that quite yet.
- the smaller the main key, the faster the processing. You may need to "optimize" the main area for just ONE of those clauses (whichever one is the most work), and then consider pushing the others into INCLUDE.
- EXCEPTION - if a field is to be used in a situation causing a scan (i.e. CASE or function in the WHERE clause), drop it in the INCLUDE. It will still be a scan, but an INDEX scan.
Finally - remember, if your indexes are "too fat" relative to the table width, the optimizer may not use them at all. (after all - if you have a table with 3 fields, and an index with ALL three in it, the index is WIDER than the table, so the compiler might just decide a table scan is faster).
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
November 8, 2007 at 7:21 pm
The difference between a covering index and an index using INCLUDES is that in a covering index all columns in the index are included in the b-tree. This makes the b-tree much larger and slower.
The index that uses INCLUDES does not index the does not include the columns in the INCLUDES clause. These columns are actually only in the lead node of the index. This is a little bit like that column of a table when you are using a clustered index - the columns are actually in the leaf of the clustered index.
One of the other benefits of using INCLUDES is that the maximum size of the columns in the index (total of 900 bytes) was a problem before if you needed to have a covering index for large column. This restriction still exists but it only applies to the columns that are actually indexed.
November 8, 2007 at 11:05 pm
One other difference is in the sorting ... if the column is included then it won't influence the order of the index records.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply