March 4, 2002 at 8:46 pm
With indexes having composite keys, why is it more efficient to search in order of most coarse to least coarse?
Example: a City, State index should be engineered as State, City for more efficient searching, but why? Won't the same number of index pages exist with either construction? For a single search, the only advantage may be in the string comparisions. State-City could compare quicker and resolve key matching (i.e. string comparison) a bit quicker.
There are other obvious advantages to having a state-city index, like grouping by state. But I don't see eliminating any "hops" throught the index structure by having a state-city index. Any help?
March 5, 2002 at 3:25 pm
Sorry, can I ask where you came across this. I have read in serveral places nothing like this only that when creating a composite index you should make the first column in the index your most selective (so if more quiries use City then City, State or if more use State then State,City). The reason is statistics are only kept for the first column, this is also the reason why you may consider not using a composite index.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
March 5, 2002 at 3:43 pm
you might have them. Consider that portland has both a Maine and an Oregon entry. The index structure could be more compact since there could be more cities under Oregon. However, under Portland, there would be only two. This could end up with more physical entries to resolve the index.
Not necessarily thought. With a 100% fillfactor, it might be the same.
Steve Jones
March 5, 2002 at 3:43 pm
quote:
so if more quiries use City then City, State or if more use State then State,City
Scratch that and sorry, had to puyt dinner on and rushed. If you only service 1 city per state then the above applies. However You would normally always want City first as most likely you have a larger number of unique city names as compared to states. The reason is when the statistics are stored it is roughly a degree of uniqueness marked for the query engine. The engine will look at all applicable indexes and there statistics based on you query, then whatever index has the highest density (most unique items) it will be considered less costly to use that index than another (this is why you don't index bit fields as they are 50/50 and most likely the index will never be used). The key in a composite index is you most unique item first to you least unique, thus giving the index a higher density in statistics and increasing it's chance to be used in a query plan otherwise you end up adding index hints to force the plan.
Hope this makes better sense and helps.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
March 5, 2002 at 3:49 pm
Be interesting to dig a little deeper, prove or disprove the theory! Of course another part of it is that the query planner wont use the index if you're searching for state and state is the 2nd col in the index.
Andy
March 5, 2002 at 4:13 pm
You know I always thought that as well, but if you look at a composite index under Show Execution Plan when quering for say State even if it is column 2 it will still use it more often than not.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
March 5, 2002 at 4:50 pm
Really...thats what I get for not double checking.
Andy
March 5, 2002 at 5:07 pm
I noticed something similar investigating the use of a stored procedure with two queries and switching. The sproc still appeared to alter its plan without a recompile. Not sure, but I suspect that either two plans are being stored, or the optimizer is doing more work. Makes queries easier, but as we get busier systems, we lose the ability to "tune" the server. Not sure if this is a good thing or bad thing.
Steve Jones
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply