Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

migrated from stackoverflow.com Jun 28 '12 at 21:50

This question came from our site for professional and enthusiast programmers.

    
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. –  David Manheim Jun 27 '12 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. –  Emmad Kareem Jun 28 '12 at 22:28

2 Answers 2

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!

share|improve this answer
    
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 Jun 27 '12 at 18:48
up vote 0 down vote accepted

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.

share|improve this answer
    
Thanks Bill, I'll play with them and see what happens! –  AzHole Jun 27 '12 at 18:48

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.