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:
In my case, retrieval speed is the priority over index size and/or write speed, but feel free to generalize.
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.