4

I am trying to figure out the best way to handle the indexes on a table in SQL Server.

I have a table that only needs to be read from. No real writing to the table (after the initial setup).

I have about 5-6 columns in the table that need to be indexed. Does it make more sense to setup one nonclustered index for the entire table and add all the columns that I need indexed to that index or should I set up multiple nonclustered indexes each with one column?

I am wondering which setup would have better read performance.

Any help on this would be great.

UPDATE:

There are some good answers already but I wanted to elaborate on my needs a little more.

There is one main table with auto records. I need to be able to perform very quick counts on over 100MM records. The where statements will vary but I am trying to index all of the possible columns in the where statement. So I will have queries like:

SELECT COUNT(recordID)
FROM tableName
WHERE zip IN (32801, 32802, 32803, 32809) 
AND makeID = '32' 
AND modelID IN (22, 332, 402, 504, 620)

or something like this:

SELECT COUNT(recordID)
FROM tableName
WHERE stateID = '9' 
AND classCode IN (3,5,9) 
AND makeID NOT IN (55, 56, 60, 80, 99)

So there is about 5-6 columns that could be in the where clause but it will vary a lot on which ones.

Sequenzia
  • 2,333
  • 9
  • 40
  • 59
  • 2
    You'll need to provide more info, as the answer depends on the type of queries you will run, and what the data looks like, esp. cardinality. Additionally, you may benefit from computed columns, indexed views, or full-text indexes. No way to say with the minimal information you have provided. – D'Arcy Rittich Oct 10 '12 at 16:56
  • 1
    Choosing your [clustered index](http://stackoverflow.com/questions/11787797) carefully is pretty important for read performance as well. – Tim Lehner Oct 10 '12 at 17:01

2 Answers2

6

The fewer indexes you have - the better. Each index might speed up some queries - but it also incurs overhead and needs to be maintained. Not so bad if you don't write much to the table.

If you can combine multiple columns into a single index - perfect! But if you have a compound index on multiple columns, that index can only be used if you use/need the n left-most columns.

So if you have an index on (City, LastName, FirstName) like in a phone book - this works if you're looking for:

  • everyone in a given city
  • every "Smith" in "Boston"
  • every "Paul Smith" in "New York"

but it cannot be used to find all entries with first name "Paul" or all people with lastname of "Brown" in your entire table; the index can only be used if you also specify the City column

So therefore - compound indexes are beneficial and desirable - but only if you can really use them! Having just one index with your 6 columns does not help you at all, if you need to select the columns individually

Update: with your concrete queries, you can now start to design what indexes would help:

SELECT COUNT(recordID)
FROM tableName
WHERE zip IN (32801, 32802, 32803, 32809) 
AND modelID = '32' 
AND model ID IN (22, 332, 402, 504, 620)

Here, an index on (zip, modelID) would probably be a good idea - both zip and modelID are used in the where clause (together), and having the recordID in the index as well (as an Include(RecordID) clause) should help, too.

SELECT COUNT(recordID)
FROM tableName
WHERE stateID = '9' 
AND classCode IN (3,5,9) 
AND makeID NOT IN (55, 56, 60, 80, 99)

Again: based on the WHERE clause - create an index on (stateID, classCode, makeID) and possibly add Include(RecordID) so that the nonclustered index becomes covering (e.g. all the info needed for your query is in the nonclustered index itself - no need to go back to the "base" tables).

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • Thanks for the help. That makes sense but I am not sure the best way to do this because I'm dealing with upto 6 columns in the WHERE clause. There is too many possible combinations for me to create a index for each. If I just put all the possible columns in one index and the recordID as the include would that be a problem. Could it slow queries down? – Sequenzia Oct 10 '12 at 22:06
  • @Sequenzia: the idea of having "just an index with all columns" will **NOT** be beneficial at all. If you're lucky, you might get the combinations right for one case - and in that case, it will be used - but having an index just over all the possible columns **IS NO** good idea at all! Don't do it! Better have no indexes than such an index! – marc_s Oct 11 '12 at 04:50
  • 1
    thanks for the follow up. I see what you are saying. I have done more research and some testing. I see where that approach would not work. I am going to try to nail down my query requirements more and build indexes around that. I appreciate your help. – Sequenzia Oct 11 '12 at 17:05
4

It depends on your access pattern

For a read only table, I'd most likely create multiple non-clustered indexes, each having multiple key columns to match WHERE clauses, and INCLUDEd columns for non-key columns

I would have neither one non-clustered for all nor one per column: they won't be useful.actual queries

Community
  • 1
  • 1
gbn
  • 422,506
  • 82
  • 585
  • 676