Question Index Choice Question

Grant Holman · Sep 29, 2015

Hypothetical:

Table X has 4 char fields A,B,C and D.

I need to index on A + B + C and A + B + D. What are the appropriate indexes to create for the table:

Choice 1:
Index 1 A + B
Index 2 C
Index 3 D

Choice 2:
Index 1 A + B + C
Index 2 A + B + D

I'm thinking Choice 1 as Progress will bracket on multiple indexes. Maybe both are wrong! Let me know your thoughts.

TheMadDBA · Sep 29, 2015

If you are only doing equality matches then choice one will work... as soon as you implement a range of any kind then choice 2 would work while choice 1 would cause excessive reads and bad index selection.

Grant Holman · Sep 30, 2015

TheMadDBA said:
If you are only doing equality matches then choice one will work... as soon as you implement a range of any kind then choice 2 would work while choice 1 would cause excessive reads and bad index selection.

It would be doing range matching on field A, so choice 2 then. Thanks.

Rob Fitzpatrick · Sep 30, 2015

If you're doing range matching on A and it is the leading component of your two indexes, that is as far as you will bracket. Having B, C, and/or D in your WHERE clause won't help you bracket further.

TheMadDBA · Sep 30, 2015

If you are doing range matching on A then like Rob says not much is going to help.... as soon as you stop using equality matches in an index the database stops using the rest of the index columns,

Either you need to move column A down in the index (with compound indexes) or you need to change those ranges into equality matches (either style).

If you posted sample queries we could probably help and explain a little better.

Grant Holman · Oct 6, 2015

TheMadDBA said:
If you are doing range matching on A then like Rob says not much is going to help.... as soon as you stop using equality matches in an index the database stops using the rest of the index columns,

Either you need to move column A down in the index (with compound indexes) or you need to change those ranges into equality matches (either style).

If you posted sample queries we could probably help and explain a little better.

Hi, its was just a hypothetical, not a real world requirement. Interested to hear that as soon as you range match the rest of the index becomes irrelevant, don't think I properly appreciated that.

Question Index Choice Question

Grant Holman

New Member

TheMadDBA

Active Member

Grant Holman

New Member

Rob Fitzpatrick

ProgressTalk.com Sponsor

TheMadDBA

Active Member

Grant Holman

New Member

Similar threads