Post reply

Indexes - Single or Composite

  • July 3, 2012 at 4:17 am

    #392646

    We are having some performance issues with selecting data from certain tables. Essentially the queries have WHERE clauses like Date BETWEEN a And B and Database = 'X'.

    I have checked the source tables and most of the tables do not have indexes on these columns. However there are some tables that have both composite indexes that includes the Database column, as well as individual indexes with just the Database column.

    If the Database column is included in a composite index and is listed first, would I also need a separate index just for the Database column?

    I know it depends on how data is being selected etc, but what are the benefits of having a mixture of composite indexes and single indexes that contain the same column, as I'm not sure whether the indexes have been applied in the most efficient way.

  • July 3, 2012 at 4:28 am

    #1508136

    Depends on selectivity (most distinct values goes) and the number of rows returned

    WHERE clauses like Date BETWEEN a And B and Database = 'X'.

    Assuming the most popular query relies on date a clustered index on date is probably a safe bet.

    If you don't need other values than Date and Database in your select you could consider including database in the index as wel to avoid an extra lookup.

    Index (date (highly selective),database) rather than index(database (low selectivity), date).

    Consider join candidates like foreign keys as index candidates.

  • July 3, 2012 at 6:27 am

    #1508200

    http://sqlinthewild.co.za/index.php/2010/09/14/one-wide-index-or-multiple-narrow-indexes/

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

    http://sqlinthewild.co.za/index.php/2009/02/06/index-columns-selectivity-and-inequality-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

  • July 3, 2012 at 8:30 am

    #1508269

    Thanks Gail. So from the first article if a WHERE clause has the two columns it seems that a single index containing Date and Database would be better?

    However, if the WHERE clause only filters on the one column would it still use the above index? ie there would be no need to create an additional single column index?

  • July 3, 2012 at 8:32 am

    #1508271

    hindle.steve (7/3/2012)

    However, if the WHERE clause only filters on the one column would it still use the above index? ie there would be no need to create an additional single column index?

    See the second blog post.

    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

  • July 3, 2012 at 9:29 am

    #1508293

    Jo Pattyn (7/3/2012)

    Depends on selectivity (most distinct values goes) and the number of rows returned

    WHERE clauses like Date BETWEEN a And B and Database = 'X'.

    Assuming the most popular query relies on date a clustered index on date is probably a safe bet.

    If you don't need other values than Date and Database in your select you could consider including database in the index as wel to avoid an extra lookup.

    Index (date (highly selective),database) rather than index(database (low selectivity), date).

    Consider join candidates like foreign keys as index candidates.

    Going to have to say "it depends" when it comes to this:

    Index (date (highly selective),database) rather than index(database (low selectivity), date).

    If all the queries on the table use WHERE Date BETWEEN a AND b and Database = 'X', the the second index (index(Datebase, Date)) actually makes sence as you restrict the query to only those records where Database = 'X'. If some queries include the Database column in the WHERE clause and others don't but all still do a ranged search on Date, then I'd go with the first index (index(Date, Database)).

    Determining what columns to index and how really comes down to knowing how the data is going to be accessed.

    There are also other considerations including data volume, data volitility (how much and how often it changes) to think of a couple off the top of my head.

  • July 3, 2012 at 9:35 am

    #1508295

    Thanks Lyn.

    I've managed to analyse some of the execution plans and changed the indexed columns. Having a covering on index(Date, Database)) is the best solution.

  • July 3, 2012 at 9:38 am

    #1508296

    hindle.steve (7/3/2012)

    Thanks Lyn.

    I've managed to analyse some of the execution plans and changed the indexed columns. Having a covering on index(Date, Database)) is the best solution.

    That's what matters. Good to see that there was analyse completed to determine the best set of indexes.

Viewing 8 posts - 1 through 7 (of 7 total)

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