Index Design - Forum - OpenEdge Development - Progress Community
 Forum

Index Design

This question is not answered

I have a chance to create/revise indexes on a Progress file, and I want to do it as thoughtfully as possible. The file has a unique key field, which is already the unique, primary index. There are 4 other relevant fields: 1: a unique identifier into a related file, 2: a 1-letter code, 3: a multi-letter code, 4: a date. Most "finds" or "for eaches" contain exact equality matches on the first 2 of those 4 fields, Field 3 is a mixed bag: sometimes it is omitted, sometimes it participates in an inequality search (e.g., "field 3 <> 'XYZ') and sometimes it's in an equality match. Field 4 is almost always used, but usually in a range. So I assume 1 new index should begin with Fields 1 & 2, and that the order doesn't matter. My thoughts/questions are:

  1. Should Field 3 be tacked onto the end of that new index?
  2. Should Field 4 be tacked onto the end of the new index (with or without Field 3)?
  3. What are the pros/cons of adding the unique field to the end of new indexes (remembering that it's already the unique primary)? (This would make the new indexes unique indexes.)
  4. Should Field 3 be set up as its own index (either with or without the unique field, depending on how you answered #3)? Obviously as a non-unique key if the unique field is not included.
  5. Same as previous question but for Field 4?

In my case, retrieval speed is the priority over index size and/or write speed, but feel free to generalize.

All Replies
  • Hi,

    Without knowing anything about the amount of records, or type or reads, I would say: why not create all relevant indices?

    In my experience adding 2 ,4 , 6 or 8 indices doesn't make a noticable difference when creating/updating a record, but can make finds / for eaches a lot faster. And if in the future, you change the business logic slightly, you have the required indices already created.

  • It also depends on the distribution of the data. And nr of records. If you have a table with - say - a few hundred records, why bother at all; just define a unique index and leave it at that. That is, if it is not accessed too much. On the other hand: if this is a really **huge** table, with lots and lots of write operations, you want less indexes, whereas if it is **huge** and has lots and lots of read operations, you probably want more.

    Without knowing how the data is composed or used, it is nearly impossible to give a good advice.

  • If you can make it to Dublin for EMEA PUG Challenge, there will be a very good workshop on Indexes given by Peter Judge and Paul Koufalis.