|
View previous topic :: View next topic
|
| Author |
Message |
rakesh1155
New User
Joined: 21 Jan 2009
Posts: 84
Location: India
|
|
|
|
Hi,
I have a doubt.
Lets say I have a table name TABLEA having 5 columns ( col_a, col_b, col_c, col_d, col_e).
I have a query like below:
SELECT * from TABLEA
where col_a = something
and col_b = something
and col_c = something
For a scenario like this,
What kind of index would give me better query performance ?
1. A single index as col_a, col_b and col_c as a composite non-unique index.
OR
2. Three indexes as col_a as a non-unique index, col_b as a non-unique index, col_c as a non-unique index.
I have an understand that a single index with the three columns would give a better query performance but would like to have a confirmation from the experts.
Thanks in advance.
Rakesh. |
|
| Back to top |
|
 |
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
I'd use the composite index, but i'd also ensure the definition placed the most unique column first, the next most unique column second, and the least unique column third.
For example if the columns are for country, region, and custid, i'd define this as custid, then region, then country. |
|
| Back to top |
|
|
rakesh1155
New User
Joined: 21 Jan 2009
Posts: 84
Location: India
|
|
|
|
| Thank You for confirming this. |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
| dick scherrer wrote: |
Hello,
I'd use the composite index, but i'd also ensure the definition placed the most unique column first, the next most unique column second, and the least unique column third.
For example if the columns are for country, region, and custid, i'd define this as custid, then region, then country. |
In my experience most reports on this kind of tables are sorted on country/region/custid.
and many (less frequent ?) queries only supply the country or country/region.
The performance gain for a query that supplies all 3 values with an index custid,region,country opposed to an index country/region/custid is in my experience very small .
especially considered the performance loss on queries with the sorts or queries that supply only country/region.
Indexes required also depends on R.I. so if you have R.I to country and to region and to custid then you might need all 3 indexes. or at least the index to the table where most deletes happen. which in this case would probably be customer.
So arguments for both cases. and as usual the final answer is : "it depends". |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hi Guy,
| Quote: |
| So arguments for both cases. and as usual the final answer is : "it depends". |
Yup. . . 
It was my understanding (possibly not correct/complete) that the reason for this key was for this particular query. If it will be used for more, more considertion would be in order.
As you say - "it depends".
d |
|
| Back to top |
|
|
rakesh1155
New User
Joined: 21 Jan 2009
Posts: 84
Location: India
|
|
|
|
Well... I agree "It depends in most cases"
Thank You Guys!! |
|
| Back to top |
|
|
|
|
