order of columns in composite index

I'm know that for composite indexes, the recommendation is always to specify
the most selective column first, but can someone please explain why this
makes such a huge difference in perfomance' I have a table where column A
only has 1 unique value and column B is basically unique among all rows. A
query that has A and B in the WHERE clause takes ALOT longer if my composite
index was created with (A, B) instead of (B, A). Thanks for any help.
BobBy the way, I did verify that in both cases, my index is being used, since I
know that index statistics are gathered based on the first column.
"Bob Gabor" <rjg@.mindspring.com> wrote in message
news:Yclkf.8556$N45.2454@.newsread1.news.atl.earthlink.net...
> I'm know that for composite indexes, the recommendation is always to
> specify the most selective column first, but can someone please explain
> why this makes such a huge difference in perfomance' I have a table
> where column A only has 1 unique value and column B is basically unique
> among all rows. A query that has A and B in the WHERE clause takes ALOT
> longer if my composite index was created with (A, B) instead of (B, A).
> Thanks for any help.
> Bob
>|||It depends on the query. Rules like the one you quote are just
general guidelines. For example, if one of the columns is used
in a range or LIKE comparison, it may be best to index that column
first regardless of selectivity. Depending on the query and data,
the two-column statistics may be more or less accurate predictors
of row count for the index in one order than in the other, also.
If you look at the query plans in more detail, you may be able
to see whether the faster solution is resulting in a better plan that
the other ordering can't allow, or if the faster solution is a result
of better row count estimates.
Steve Kass
Drew University
Bob Gabor wrote:

>I'm know that for composite indexes, the recommendation is always to specif
y
>the most selective column first, but can someone please explain why this
>makes such a huge difference in perfomance' I have a table where column A
>only has 1 unique value and column B is basically unique among all rows. A
>query that has A and B in the WHERE clause takes ALOT longer if my composit
e
>index was created with (A, B) instead of (B, A). Thanks for any help.
>Bob
>
>|||>> is always to specify
the most selective column first, but can someone please explain why
this
makes such a huge difference in perfomance' <<
there is only one hard and fast rule in our trade:
there are no hard and fast rules in database programming.
;)
For instance, if you frequently join on some column, putting it first
frequently speeds up joins.|||It does depend on many things, but I can give you some insight into why this
might be a problem (though I can not say it is necessarily a problem for
your application).
If you have a 2-column index with a non-selective leading column and a very
selective secondary column, it can cause additional I/O when compared to a
query run over an index with the columns defined in the opposite order
(selective column first). If the query does a s on the first column and
then later columns, this could be less efficient.
SELECT col1, col2 FROM Table WHERE col1=4 and col3 > 5;
I will point out that it can vary from database engine to database engine.
It can vary on the predicates being used. It can vary based on the mix of
queries and the hardware. In short, it really does depend. However, it is
generally good to index selective fields since the cost of searching and the
cost of maintaining these indexes in updates is less than non-selective
columns.
Another reason to potentially pick a more selective leading index column,
all other factors being equal, is that SQL Server builds histograms on the
leading column. If it is very unselective, this can make the process of
cardinality estimation more difficult for the optimizer. This could cause
errors that lead to less than optimal plans being picked in some cases.
I hope that this gives you some insights into the internals to understand
why it might matter.
Thanks,
Conor Cunningham
SQL Server Query Optimization Development Lead
"Alexander Kuznetsov" <AK_TIREDOFSPAM@.hotmail.COM> wrote in message
news:1133653242.800468.130470@.g47g2000cwa.googlegroups.com...
> the most selective column first, but can someone please explain why
> this
> makes such a huge difference in perfomance' <<
> there is only one hard and fast rule in our trade:
> there are no hard and fast rules in database programming.
> ;)
> For instance, if you frequently join on some column, putting it first
> frequently speeds up joins.
>

order by

Hi all,
I Apologize if I'm not clear, I'm going to try and explain what I need.
This query works fine: On the table "k" there is a display_order column
that I would like the k.sentence_name ordered by.
If I add vk.display_order in the order by, I have to add it as well in the
select but, this screws up using Distinct in the Select?
The display_order column basically stores a number value indicating the
order of the k.sentence_name is shown to the user in a application,
I would also like to order it that way in the return results.
SELECT DISTINCT k.kb_Classification_ekey AS [Classification key],
c.classification_name AS [Classification Name], vm.menu_name AS [Menu Name],
k.sentence_name AS [Child Menu Name]
FROM
ingres_keyword AS k, ingres_kb_classification AS c,
ingres_view_keyword AS vk, ingres_view_menu AS vm
WHERE
k.kb_classification_ekey=c.ekey And
k.ekey=vk.keyword_ekey And vm.ekey=vk.view_menu_parent_ekey And
k.kb_classification_ekey In (129,229,279,294,312,313,314,315,320)
ORDER BY k.kb_classification_ekey, vm.menu_name, k.sentence_name;
thanks
gvSELECT k.kb_Classification_ekey AS [Classification key],
c.classification_name AS [Classification Name],
vm.menu_name AS [Menu Name],
k.sentence_name AS [Child Menu Name]
FROM
ingres_keyword AS k, ingres_kb_classification AS c,
ingres_view_keyword AS vk, ingres_view_menu AS vm
WHERE
k.kb_classification_ekey=c.ekey And
k.ekey=vk.keyword_ekey And vm.ekey=vk.view_menu_parent_ekey And
k.kb_classification_ekey In (129,229,279,294,312,313,314,315,320)
group by k.kb_Classification_ekey,c.classification_name,vm.menu_name AS
[Menu Name],
k.sentence_name
ORDER BY k.kb_classification_ekey, vm.menu_name,
k.sentence_name,max(vk.display_order);
-oj
"gv" <viatorg@.musc.edu> wrote in message
news:uHNlqBTxFHA.3236@.TK2MSFTNGP14.phx.gbl...
> Hi all,
> I Apologize if I'm not clear, I'm going to try and explain what I need.
> This query works fine: On the table "k" there is a display_order column
> that I would like the k.sentence_name ordered by.
> If I add vk.display_order in the order by, I have to add it as well in the
> select but, this screws up using Distinct in the Select?
> The display_order column basically stores a number value indicating the
> order of the k.sentence_name is shown to the user in a application,
> I would also like to order it that way in the return results.
> SELECT DISTINCT k.kb_Classification_ekey AS [Classification key],
> c.classification_name AS [Classification Name], vm.menu_name AS [Menu
> Name],
> k.sentence_name AS [Child Menu Name]
> FROM
> ingres_keyword AS k, ingres_kb_classification AS c,
> ingres_view_keyword AS vk, ingres_view_menu AS vm
> WHERE
> k.kb_classification_ekey=c.ekey And
> k.ekey=vk.keyword_ekey And vm.ekey=vk.view_menu_parent_ekey And
> k.kb_classification_ekey In (129,229,279,294,312,313,314,315,320)
> ORDER BY k.kb_classification_ekey, vm.menu_name, k.sentence_name;
> thanks
> gv
>
>
>|||cut&paste prob..here's the correction.
SELECT k.kb_Classification_ekey AS [Classification key],
c.classification_name AS [Classification Name],
vm.menu_name AS [Menu Name],
k.sentence_name AS [Child Menu Name]
FROM
ingres_keyword AS k, ingres_kb_classification AS c,
ingres_view_keyword AS vk, ingres_view_menu AS vm
WHERE
k.kb_classification_ekey=c.ekey And
k.ekey=vk.keyword_ekey And vm.ekey=vk.view_menu_parent_ekey And
k.kb_classification_ekey In (129,229,279,294,312,313,314,315,320)
group by
k.kb_Classification_ekey,c.classification_name,vm.menu_name,k.sentence_name
ORDER BY k.kb_classification_ekey, vm.menu_name,
k.sentence_name,max(vk.display_order);
-oj
"oj" <nospam_ojngo@.home.com> wrote in message
news:e4wVW5TxFHA.3152@.TK2MSFTNGP10.phx.gbl...
> SELECT k.kb_Classification_ekey AS [Classification key],
> c.classification_name AS [Classification Name],
> vm.menu_name AS [Menu Name],
> k.sentence_name AS [Child Menu Name]
> FROM
> ingres_keyword AS k, ingres_kb_classification AS c,
> ingres_view_keyword AS vk, ingres_view_menu AS vm
> WHERE
> k.kb_classification_ekey=c.ekey And
> k.ekey=vk.keyword_ekey And vm.ekey=vk.view_menu_parent_ekey And
> k.kb_classification_ekey In (129,229,279,294,312,313,314,315,320)
> group by k.kb_Classification_ekey,c.classification_name,vm.menu_name AS
> [Menu Name],
> k.sentence_name
> ORDER BY k.kb_classification_ekey, vm.menu_name,
> k.sentence_name,max(vk.display_order);
> --
> -oj
>
> "gv" <viatorg@.musc.edu> wrote in message
> news:uHNlqBTxFHA.3236@.TK2MSFTNGP14.phx.gbl...
>|||Thanks oj for your help,
I just relized that I posted this in the sqlserver programming group, I'm
doing this in Access 2003 and
gives me an error that I cannot use max in the order by clause.
Any suggestions on what to do in Access? sorry
thanks again
gv
"oj" <nospam_ojngo@.home.com> wrote in message
news:e4wVW5TxFHA.3152@.TK2MSFTNGP10.phx.gbl...
> SELECT k.kb_Classification_ekey AS [Classification key],
> c.classification_name AS [Classification Name],
> vm.menu_name AS [Menu Name],
> k.sentence_name AS [Child Menu Name]
> FROM
> ingres_keyword AS k, ingres_kb_classification AS c,
> ingres_view_keyword AS vk, ingres_view_menu AS vm
> WHERE
> k.kb_classification_ekey=c.ekey And
> k.ekey=vk.keyword_ekey And vm.ekey=vk.view_menu_parent_ekey And
> k.kb_classification_ekey In (129,229,279,294,312,313,314,315,320)
> group by k.kb_Classification_ekey,c.classification_name,vm.menu_name AS
> [Menu Name],
> k.sentence_name
> ORDER BY k.kb_classification_ekey, vm.menu_name,
> k.sentence_name,max(vk.display_order);
> --
> -oj
>
> "gv" <viatorg@.musc.edu> wrote in message
> news:uHNlqBTxFHA.3236@.TK2MSFTNGP14.phx.gbl...
>|||Try:
select [Classification key],[Classification Name],
[Menu Name],[Child Menu Name]
from (
SELECT k.kb_Classification_ekey AS [Classification key],
c.classification_name AS [Classification Name],
vm.menu_name AS [Menu Name],
k.sentence_name AS [Child Menu Name],
max(vk.display_order) as [ord]
FROM
ingres_keyword AS k, ingres_kb_classification AS c,
ingres_view_keyword AS vk, ingres_view_menu AS vm
WHERE
k.kb_classification_ekey=c.ekey And
k.ekey=vk.keyword_ekey And vm.ekey=vk.view_menu_parent_ekey And
k.kb_classification_ekey In (129,229,279,294,312,313,314,315,320)
group by
k.kb_Classification_ekey,c.classification_name,vm.menu_name,k.sentence_name
) as derived
ORDER BY [Classification key],[Classification Name],[Child Menu Name],[ord]
-oj
"gv" <viatorg@.musc.edu> wrote in message
news:OHaOF$TxFHA.2076@.TK2MSFTNGP14.phx.gbl...
> Thanks oj for your help,
> I just relized that I posted this in the sqlserver programming group, I'm
> doing this in Access 2003 and
> gives me an error that I cannot use max in the order by clause.
> Any suggestions on what to do in Access? sorry
> thanks again
> gv
>
> "oj" <nospam_ojngo@.home.com> wrote in message
> news:e4wVW5TxFHA.3152@.TK2MSFTNGP10.phx.gbl...
>|||Thanks again oj for your help
still same error. that error in Access 2003 is
Use of aggregates inside the order by clause
is illegal. (#328618)
thanks
gv
"oj" <nospam_ojngo@.home.com> wrote in message
news:u%23Yz4DUxFHA.1148@.TK2MSFTNGP11.phx.gbl...
> Try:
> select [Classification key],[Classification Name],
> [Menu Name],[Child Menu Name]
> from (
> SELECT k.kb_Classification_ekey AS [Classification key],
> c.classification_name AS [Classification Name],
> vm.menu_name AS [Menu Name],
> k.sentence_name AS [Child Menu Name],
> max(vk.display_order) as [ord]
> FROM
> ingres_keyword AS k, ingres_kb_classification AS c,
> ingres_view_keyword AS vk, ingres_view_menu AS vm
> WHERE
> k.kb_classification_ekey=c.ekey And
> k.ekey=vk.keyword_ekey And vm.ekey=vk.view_menu_parent_ekey And
> k.kb_classification_ekey In (129,229,279,294,312,313,314,315,320)
> group by
> k.kb_Classification_ekey,c.classification_name,vm.menu_name,k.sentence_nam
e
> ) as derived
> ORDER BY [Classification key],[Classification Name],[Child Menu
> Name],[ord]
>
> --
> -oj
>
> "gv" <viatorg@.musc.edu> wrote in message
> news:OHaOF$TxFHA.2076@.TK2MSFTNGP14.phx.gbl...
>|||Just did a quick test. It worked for me here.
SELECT CustomerID,OrderID
FROM Orders
GROUP BY CustomerID,OrderID
ORDER BY CustomerID,OrderID,Max(EmployeeID);
Can you post the exact query that generates the error.
-oj
"gv" <viatorg@.musc.edu> wrote in message
news:%23Gl21RUxFHA.2880@.TK2MSFTNGP12.phx.gbl...
> Thanks again oj for your help
> still same error. that error in Access 2003 is
> Use of aggregates inside the order by clause
> is illegal. (#328618)
> thanks
> gv
>
> "oj" <nospam_ojngo@.home.com> wrote in message
> news:u%23Yz4DUxFHA.1148@.TK2MSFTNGP11.phx.gbl...
>sql