Consider the following table:

i | a | b | c
0 | 1 | 2 | 3
1 | 4 | 5 | 6
2 | 7 | 8 | 9
3 | x | y | z

The table, in the end, will have several thousand rows, and even more columns then described here (20+). I plan on adding indexes to the table, but cannot figure out which indexes to make. In the end, I'll be doing SELECT statements with different combinations of these, and sometimes only on 1 column. Is the most efficient way to make the indexes:

(A) Index each column separately, eg. aIndex(a), bIndex(b), cIndex(c), etc..

(B) An Index for each type of select statement, eg. aIndex(a), abIndex(a,b), acIndex(a,c), etc..

(C) Just index them all together into one, eg. tIndex(a,b,c).

Indexes are one of those things I'm still getting a grasp on, so I apologize if this seems like a silly or simplistic question. What I'm trying to avoid is unnecessary indexes as they will increase disk-space and INSERT times dramatically.

  • This isn't the type of question you should ask on Stackoverflow - you need to learn the material, not answer a specific question, so it's not really the right format for this forum. Commented Jun 27, 2012 at 18:38
  • Remember that when you add many indexes, you insert and update performance may suffer. Be careful if the database is accessed from an on-line application.
    – NoChance
    Commented Jun 28, 2012 at 22:28

2 Answers 2


It is best to use any combination of the techniques, but match them to your actual queries. Some queries may only need a single column index, while other queries may need a mutli-column index. That is a great thing about indexes, is you can add them as you need them, and remove ones that no longer serve a purpose, so you have exactly what you need for your queries.


Just having the table definition and a few rough examples is probably not enough information to get you the answer you want. You need to know which queries are going to be the most heavily used.

For example, you only get 1 clustered index, and that's the most powerful index, so you only have one option for that.

Are all/most of your queries likely to have a particular column (i?) involved? Do you join to other tables? On a common column? That would lead me to think i should be the first column in the clustered index. The subsequent columns in that index, should be the next most-commonly used columns (taking cardinality into consideration). You can put all the columns in the clustered index if you like, as that's effectively the order the data is stored in. So the order of the columns is NB.

Then, single column indexes can reference the clustered index, so that helps. But your choice of those non-clustered indexes and the number/order of columns also depends on the expected usage.

Also, is this a data entry or reporting table? Data entry = more indexes make it slower to enter data. Reporting = index the hell out of it, if needed!

  • Thanks Sean, a lot to look at and think. All the queries will include column (i) in the example and no other tables are involved, so no joins. And it's a data entry table, not a report table. I'll do some more reading and see what I can get out of it. Appreciate the time!
    – AzHole
    Commented Jun 27, 2012 at 18:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.