Michael Maddison
michael at ddisolutions.com.au
Tue Dec 18 18:46:30 CST 2007
Hi John, Came across this discussion on another list. Thought it might be helpful. cheers Michael M I believe Tom is partly correct. But if I build a covering index on LastName, FirstName, Birthdate, it will cover queries that involve LastName only in the WHERE clause and it will cover a query that looks for LastName and FirstName as well as one for LastName, FirstName and Birthdate. It will also cover a query that looks for LastName, Birthdate. It may not cover a query that only looks for FirstName or one that looks for FirstName, BirthDate. Only the query plan would show whether it does or not. The optimizer does keep overall density statistics on the subordinate keys, but not detailed statistics. This is a place where I think column statistics might be useful. Sharon ------------------------------------------------------------------------ -------- From: listbounces at sswug.org [mailto:listbounces at sswug.org] On Behalf Of Tom Cooper Sent: Tuesday, December 18, 2007 12:34 PM To: sql2k5 at sswug.org Subject: RE: [SQL2K5] compound index vs singleton? I agree with Adi also. The kindest thing I can say about my answer is it was not clear. I was responding to the previous message in the thread from Drew where he was concerned with covered queries. So I was talking about covered queries only. But I did not specify that. And that was misleading since the typical query does not have a covered index. And if the index does not cover the query than the order of the columns in the index can make a huge difference in the performance since, as Kenneth pointed out, SQL keeps statistics only on the first column, and if the first column not selective enough, SQL will not use the index, but will instead scan the table. But the situation is different for a covered query. AFAIK, SQL will not look at the statistics for the index for a covered query, since the index is covers the query and there is no need to go to the table at all. So if your index is on Country, FName, and LName, in any order, then for the query Select Country, LName, FName >From TestTable Where Country = 'US' And LName = 'Cooper' And FName = 'Tom' And the table has X rows with N rows matching the WHERE condition. Then in the index, those N rows will be together in the index (no matter what the order of the columns in the index), and SQL will seek to the first row in the index that matches the condition, scan thru only those N rows and then stop the scan and return the result. So in that very restricted case, the order of the columns in the index makes no difference (except, of course, if that covered query has an ORDER BY, in which case, the best of all possible worlds would be to have the columns in the index in the same order as the columns in the ORDER BY, so SQL would not have to do a sort before returning the results). I probably should have just said "it depends" <grin> Tom ------------------------------------------------------------------------ -------- From: listbounces at sswug.org [mailto:listbounces at sswug.org] On Behalf Of Sharon F. Dooley Sent: Tuesday, December 18, 2007 5:16 AM To: sql2k5 at sswug.org Subject: RE: [SQL2K5] compound index vs singleton? I agree with Adi. The order does make a big difference. I once solved a performance problem by moving what seemed to be the most minor of several columns to the beginning of the index. It made a huge difference. Sharon ------------------------------------------------------------------------ -------- From: listbounces at sswug.org [mailto:listbounces at sswug.org] On Behalf Of Adi Cohn Sent: Tuesday, December 18, 2007 2:53 AM To: sql2k5 at sswug.org Subject: RE: [SQL2K5] compound index vs singleton? While I agree with most of what you wrote, there is one statement from your message that I don't agree - "If you are referencing all of the columns in the index in your WHERE clause and all of the comparisons are for equality, then AFAIK the order of the columns in the index doesn't make a difference". In my opinion in this case you should order the column in the index according to the columns' selectivity. For example if in the table there are only 2 countries - USA and Canada, then country will be the last column in the index. Adi ------------------------------------------------------------------------ -------- From: listbounces at sswug.org [mailto:listbounces at sswug.org] On Behalf Of Tom Cooper Sent: Tuesday, December 18, 2007 3:54 AM To: sql2k5 at sswug.org Subject: RE: [SQL2K5] compound index vs singleton? If you are referencing all of the columns in the index in your WHERE clause and all of the comparisons are for equality, then AFAIK the order of the columns in the index doesn't make a difference. But if you are only referencing some of those column in the WHERE clause or some of the comparisons return a range, the order makes a difference. For example, if you have a table of everyone in the world and your query is SELECT Country, FName, LName FROM <table> WHERE Country = 'US' AND LName = 'Cooper' Then an index on (Country, LName, FName) will be much better for that query than an index on (FName, LName, Country) even though both of those would be covering indexes. With the first index, you would get a seek to the first entry for US and Cooper and then it would only have to read the index from that point until it found the first entry which was not US and Cooper. But with the second index, it would have to scan the entire index. Tom ------------------------------------------------------------------------ -------- From: listbounces at sswug.org [mailto:listbounces at sswug.org] On Behalf Of drewg1010101 at aol.com Sent: Monday, December 17, 2007 8:07 PM To: sql2k5 at sswug.org Subject: Re: [SQL2K5] compound index vs singleton? thanks very much, so for the index to be useful as a covering index, is the covering index constructed on the basis of selectivity/dispersion of values through the table OR does order of attributes at creation matter? iow, is it enough that each column referenced in each part of the query (select from where) only be included the create index statement without respect to their order in index creation statement? thanks again drew -----Original Message----- From: Sharon F. Dooley <sharond at voicenet.com> To: sql2k5 at sswug.org Sent: Mon, 17 Dec 2007 7:06 pm Subject: RE: [SQL2K5] compound index vs singleton? No way! While it may be smart and use two single-column indexes, it is not guaranteed that it will do so. If your queries involve multipart where clauses, you will definitely benefit from multi-column indexes. If you create two single-column indexes, you will also have two indexes that have to be maintained and two sets of statistics that will have to be updated. Assuming that these are non-clustered indexes, you will get the biggest bang for the buck with covering indexes. You can use SQL 2005's included columns feature to include non-key columns. Sharon ------------------------------------------------------------------------ -------- From: listbounces at sswug.org [mailto:listbounces at sswug.org] On Behalf Of drewg1010101 at aol.com Sent: Monday, December 17, 2007 4:15 PM To: sql2k5 at sswug.org Subject: [SQL2K5] compound index vs singleton? i seem to remember that SQL Server will make use of two simple indexes if the optimizer determines that is the cheapest way to resolve a query, the reason i care is because if the server will combine simple indexes appropriately, i should NOT be creating multiple column indexes is that correct? thanks drew