SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 indices... use one, use two, use three ?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sdiwi
Starting Member

Germany
27 Posts

Posted - 08/01/2005 :  04:52:52  Show Profile  Reply with Quote
hiho!

i just have a little performance question about indices - imagine following table:

mytable
-------
PK_Primary
FK_Foreign1
FK_Foreign2
someData1
someData2
.
.
.
indexHere1
indexHere2
indexHere3

in 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 columns

and if you really wanna please me, tell me why it's like that.

thanks alot.

peace,

sdiwi.

Edited by - sdiwi on 08/01/2005 04:57:06

spirit1
Cybernetic Yak Master

Slovenia
11749 Posts

Posted - 08/01/2005 :  05:13:12  Show Profile  Visit spirit1's Homepage  Reply with Quote
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
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 08/01/2005 :  10:02:14  Show Profile  Reply with Quote
As Spirit said really - dunno if my comments will add anything, but here goes nothing!

A composite index on
indexHere1
indexHere2
indexHere3
is 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
Go to Top of Page

sdiwi
Starting Member

Germany
27 Posts

Posted - 08/01/2005 :  10:57:00  Show Profile  Reply with Quote
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***
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 08/01/2005 :  12:09:43  Show Profile  Reply with Quote
"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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000