Index question

donovan7800

Small question,

If I know I will have queries like:

WHERE colA = 'XXX'
AND colB = 'YYY'
AND colC = 'ZZZ'

WHERE colA = 'XXX'

WHERE colB = 'YYY'

WHERE colC = 'ZZZ'

but I know I will NOT have any partial usage of columns like:

WHERE colA = 'XXX'
AND colB = 'YYY'

Then I only need to create a single index on (colA, colB, colC) right? 

And if I did have a 2-column WHERE conditions using the above example, then I would want to make additional indexes for (colA, colB)  (colB, colC) (colA, colC) right?

Himanshu Kandpal

Hi,

What is you are trying to get after creating an index on ColA,ColB and ColC.

Is it performance improvements in your select criteria then it depends upon lot of things like the number of rows being returned when you run WHERE colA = 'XXX'  or are there any others columns being selected or any other columns in the where clause, is this table going to have only updates or inserts.

One thing you have to keep in mind that in some circumtances a Full table read is better than a index read.

thanks

John Spencer

An index on cola, colb, colc would cover the first two queries.  You might get an index skip scan for predicates on colb or colc alone, but it would depend on a number of factors, including the the size of the table and the distribution of the column values.

I might be tempted to create additional indexes on colb alone and colc alone to cover the other singleton predicates.  That assumes that both are reasonable selective compared to the size of the table,

John

donovan7800

Yes the reason is for performance, one of these tables is 25M rows.

colA - low selectability, maybe only several distinct values

colB - high selectability, almost distinct, 1 colB will usually return 1-5 records mostly.

colC - maybe ~50 distinct values

Sounds like the best approach would be to try these indexes out and look and the xplan on DEV thx.

John Spencer

donovan7800 wrote:

Yes the reason is for performance, one of these tables is 25M rows.
colA - low selectability, maybe only several distinct values
colB - high selectability, almost distinct, 1 colB will usually return 1-5 records mostly.
colC - maybe ~50 distinct values

Sounds like the best approach would be to try these indexes out and look and the xplan on DEV thx.

If cola has few distinct values and a relatively even distribution among those values, then I would consider either colb, colc or colc,colb as a compound index, depending on the relative frequency of querying on either alone and leave cola un-indexed since it sounds like a query on cola alone would return a significant fraction of the table so a full table scan would likely be more appropriate anyway.

Having said that, you are correct in that the best approach would be to test it.

John

Unknown

Then I only need to create a single index on (colA, colB, colC) right? 

No - you do not NEED to create ANY indexes unless a column is being used to enforce a constraint.

Other than constrain support an index is a 'solution' to a 'problem'. Until you identify an actual problem you do NOT need an index.

Think about it - why would you need ANY indexes on a table with only 20 rows when all 20 rows are in ONE block? Does that makes any sense?

See the point I'm making? Without knowing the FULL details about your data and how it is used it is NOT possible to determine whether ANY indexes might be useful as part of some 'solution' to your 'unknown' problem.

I've have seen far too many developers adding indexes to tables just because they think it MIGHT help performance.