Post reply

if there is a composite index on three fields, but a query only uses one is the index used?

  • August 31, 2011 at 11:56 am

    #242632

    lets say there is a composite index on firstname, lastname, and companyid

    but i have a query that just has companyid in the where clause

    is a separate companyid index required?

  • August 31, 2011 at 12:08 pm

    #1375616

    It will probably use the composite index if the single column passed in is the leading part of the composite index (i.e. the first column in the composite).

    Otherwise, the composite index will not help at all, and will be ignored.

    ----------------------------------------------------------------------------------
    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?

  • August 31, 2011 at 12:15 pm

    #1375618

    xgcmcbain (8/31/2011)

    lets say there is a composite index on firstname, lastname, and companyid

    but i have a query that just has companyid in the where clause

    is a separate companyid index required?

    If the index is on the three columns in the order you listed - (firstname, lastname, companyid), then the best that a query filtering on just companyid would be able to do would be an index scan. To seek, the filter column(s) must be a left-based subset of the index key.

    http://sqlinthewild.co.za/index.php/2009/01/19/index-columns-selectivity-and-equality-predicates/

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply