none
how and when SQL Use Composite index

    Question

  • i am having a composite index on my Table  for example:

    CREATE  NONCLUSTERED INDEX NI_ID_FirstName
    5 > ON Employee(ID, First_Name, Last_Name, Job)

    Please can any one explain which ‘where ‘ clause will use this composite index and why

     

    1)     where ID= 1 and First_Name= XYZ

    2)     where First_Name= XYZ

    3)     where ID= 1 and Job= Sales

    4)     where ID= 1 and First_Name= XYZand Last_name='ABC'

     

    please guide me regarding the same.

    Many many thanks in advance.

     

    Regards,

    Amitesh Srivastva


     


    Regards, Amitesh Srivastva
    Wednesday, January 19, 2011 1:58 PM

Answers

  • The index might be useful for all 4 queries, but that is probably not what the author of this exam question meant.

    The queries 1, 3 and 4 can seek the index to find the qualifying rows, because ID is the leading column in the index.

    Query 1 and 4 will only find matching rows. The first match will be seeked. Any (partial) scan that follows will only find matching rows.

    Query 3 will find matching rows with (potentially) keys with different Job values than ' Sales' which need to be filtered out. So query 3 will be less efficient if this index is used. The first key with ID is "1" is seeked. The entire space for ID=1 has to be scanned to filter out the keys where Job = ' Sales'.

    Query 2 might use the index as well. However, in that case, the entire index has to be scanned, because First_Name is not the leading column in the index, so the search space cannot be reduced.

    Whether these queries will actually use the index depends on many other factors, such as the rest of the query, any other index on the table, the data distribution and more.


    Gert-Jan
    • Proposed as answer by Naomi NModerator Wednesday, January 19, 2011 7:14 PM
    • Marked as answer by Ai-hua Qiu Thursday, January 27, 2011 8:22 AM
    Wednesday, January 19, 2011 6:24 PM

All replies

  • I think It will use the index for 1,3,4  .... But there won't be any advantage of using that index for 2nd where clause....

    Since, you created the index on Id,FirstName,LastName,Job columns, it will store entries like

    Id FirstName LastName Job

    1    a             fdsf    ffsdf

    1    b             fsdfsd fsdfds

    2    a              fsdf    fsdfs

     

    Suppose, now if u search for simply firstname = 'a', it might be with any ID combination. So, it needs to perform entire index scan, which is not going to be helpful.

    It will be using this index when there is search condition involving "ID" column

    Wednesday, January 19, 2011 2:09 PM
  • 1 3 and 4 queries may use this composite index. Good introduction on this topic is in this article and this article is also good in understanding different kind of indexes in general.
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Wednesday, January 19, 2011 2:16 PM
  • >>Please can any one explain which ‘where ‘ clause will use this composite index and why

    It depends on selectivity of your data. Most likely it will use SEEK predicate for 1,3-?,4 , to cover option 2 you would have another index...

    But it is also possible that having index on ID, job will cover option 3

    Test it and make decision....


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Wednesday, January 19, 2011 2:19 PM
  • can u explain me in more details like as per u it should not use this where clause

     

    where first_name ='xyz' last_name='abcd' and Job='Sales'

     

    is it so.....!!


    Regards, Amitesh Srivastva
    Wednesday, January 19, 2011 2:34 PM
  • is it means if i use

    where First_name='XYZ' and Last_name='ABC' and Job='Sales'

    then it will not use Index, can you plese explain it in more details

     

    Thanks in advance


    Regards, Amitesh Srivastva
    Wednesday, January 19, 2011 2:38 PM
  • The first article I linked in my reply explains, that only when the first column in the composite index is used in the WHERE clause, that composite index is used. I quickly read that article and it's a good one.
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Wednesday, January 19, 2011 2:39 PM
  • how can we check that which where clause is using that composite index.

    can we check it using explain plain or any other way, please let me know in detail if there is any way to check


    Regards, Amitesh Srivastva
    Wednesday, January 19, 2011 2:46 PM
  • The Reason is simple.... Assume You have an index on 2 columns say (firstName,LastName), assume table has entire combinations. when you create an index, it will store like this..

    FirstName lastname

    a   a

    a b

    ----

    a  z

    b  a

    b b

    ---

    b  z

    ----

    ------

    z  a

    z b

    ---

    z z

     

    Now from this, if you ask it, give me the records where last name has "a", just see, what is the way here?? no way... it needs to scan all records right?? for that reason, it can't use index here..

     

    If u ask the records where firstname has "a", then it can simply search first few records and will give it.. when it finds "B", it will stop.........

    Added Later: Although it might use the index, it won't give much performance benefit... 

    • Edited by ramireddy Thursday, January 20, 2011 5:19 AM
    Wednesday, January 19, 2011 2:48 PM
  • The Reason is simple.... Assume You have an index on 2 columns say (firstName,LastName), assume table has entire combinations. when you create an index, it will store like this..

    FirstName lastname

    a   a

    a b

    ----

    a  z

    b  a

    b b

    ---

    b  z

    ----

    ------

    z  a

    z b

    ---

    z z

     

    Now from this, if you ask it, give me the records where last name has "a", just see, what is the way here?? no way... it needs to scan all records right?? for that reason, it can't use index here..

    If u ask the records where firstname has "a", then it can simply search first few records and will give it.. when it finds "B", it will stop.........

     


    Yes, but realize that Oracle has a "skip scan" which does leverage the 2nd column of an index when the first column of the index is not included in the WHERE clause.  It can provide modest performance gains when the cardinality of the first column is low -- something that would not apply in this circumstance.  Also, I have found the "skip scan" in Oracle to be of limited usefulness and I am not calling for a similar feature -- just disclosing that there are circumstances in which the index could be used and pointing out that this is more than just in theory because it is already implemented in Oracle.

    Also, I have previously posted at least one circumstance in which "skip scan" can be simulated in SQL Server to gain the performance that is possible with a skip scan.  I don't recommend it, but it is possible.

    Wednesday, January 19, 2011 3:27 PM
  • The simplest way to check which index was used with the query is to run it with include actual execution plan option and then looking at the execution plan.
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Wednesday, January 19, 2011 3:34 PM
  • In addition to the other posts, SQL Server can also use the index for 2). It can scan (not seek) the leaf level and for the rows that satisfies the WHERE clause, it will "jump down" to the data page (lookup). won't see that very frequently, though (it doesn't tend to add much value).
    Tibor Karaszi, SQL Server MVP | web | blog
    Wednesday, January 19, 2011 5:29 PM
  • The index might be useful for all 4 queries, but that is probably not what the author of this exam question meant.

    The queries 1, 3 and 4 can seek the index to find the qualifying rows, because ID is the leading column in the index.

    Query 1 and 4 will only find matching rows. The first match will be seeked. Any (partial) scan that follows will only find matching rows.

    Query 3 will find matching rows with (potentially) keys with different Job values than ' Sales' which need to be filtered out. So query 3 will be less efficient if this index is used. The first key with ID is "1" is seeked. The entire space for ID=1 has to be scanned to filter out the keys where Job = ' Sales'.

    Query 2 might use the index as well. However, in that case, the entire index has to be scanned, because First_Name is not the leading column in the index, so the search space cannot be reduced.

    Whether these queries will actually use the index depends on many other factors, such as the rest of the query, any other index on the table, the data distribution and more.


    Gert-Jan
    • Proposed as answer by Naomi NModerator Wednesday, January 19, 2011 7:14 PM
    • Marked as answer by Ai-hua Qiu Thursday, January 27, 2011 8:22 AM
    Wednesday, January 19, 2011 6:24 PM