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.
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.
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".