S
S33
Guest
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: Should Field 3 be tacked onto the end of that new index? Should Field 4 be tacked onto the end of the new index (with or without Field 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.) 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. 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.
Continue reading...
Continue reading...