Indexes - Single or Composite

Question

Post reply

Indexes - Single or Composite

hindle.steve

SSCrazy

Points: 2057
More actions
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.

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

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

Jo Pattyn SSC-Dedicated Points: 32347 More actions · Answer 1

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.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

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

hindle.steve SSCrazy Points: 2057 More actions · Answer 3

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?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

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.

hindle.steve SSCrazy Points: 2057 More actions · Answer 6

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.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

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.