Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
sdiwi
Starting Member
27 Posts |
 Posted - 2005-08-01 : 04:52:52
|
| hiho!i just have a little performance question about indices - imagine following table:mytable-------PK_PrimaryFK_Foreign1FK_Foreign2someData1someData2...indexHere1indexHere2indexHere3in order to boost the databases' speed i want to create indices for the columns indexHere1, indexHere2, indexHere3.what's better then?- creating an index for every single column- creating one index for all the columnsand if you really wanna please me, tell me why it's like that.thanks alot.peace,sdiwi. |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2005-08-01 : 05:13:12
|
well the answer is it depends  i'd start with non clustered on each column.composite index is usable only if you allways put all 3 columns in your joins.because all 3 columns are part of it, using only 2 is useless...and i haven't seen much db's which are created like that.try both cases... test them... and you'll see what's best.for better info you should post more info like what are the datatypes what kind of data will the columns hold, will the data be unique, etc...Go with the flow & have fun! Else fight the flow  |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2005-08-01 : 10:02:14
|
| As Spirit said really - dunno if my comments will add anything, but here goes nothing!A composite index onindexHere1indexHere2indexHere3is only any use for criteria on between one and all three of those columns (in the order they are defined in the index).It would also be useful for a WHERE clause, say, that used "indexHere1" and a SELECT statement that used one or more of "indexHere1" ... "indexHere3" - a Covered Query (SQL will just yuse the index and not bother to go to the actual data records)But a criteria only on "indexHere2" won't use that index.So in general my view would be that separate indexes are more likely to be useful - multiple indexes can still be used on a composite query, but its not as efficient as a single composite index.If you have individual indexes and they have large numbers of the same values in them then there is a fair chance that they won't get used at all (SQL can tell you how "selective" an index is, and the query plan will tell you which index(es) are being used.The only way to know is to test it on real-world data with real-world data volumes.In the real world that is often several months after the application went live when people start complaining that the server has become slow!Kristen |
|
|
|
sdiwi
Starting Member
27 Posts |
Posted - 2005-08-01 : 10:57:00
|
| alright. thanks for your info.you seem to be right, an unique index for every column seems to be the fastest solution (altough, there is not much difference in my case).so it behaves the way i expected...i'm starting to love "databasing".thanks for your help,peace,sdiwi ***mentally distorted*** |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-08-01 : 12:09:43
|
| "there is not much difference in my case"Well .... if you are concerned about it, and if you don't have much data, or load on the server, as yet then it would be worth checking the Query Plan Stuff for the number of Logical Reads etc and getting those figures as low as possible - that way when it scales up the improvement you make now will transfer into real time saved.Kristen |
|
|
|
|
|
|
|
|
indices... use one, use two, use three ?