2 Replies Latest reply on Mar 1, 2011 9:31 AM by philmodjunk

    best use of field indexing

    c.wagner1

      Title

      best use of field indexing

      Post

      I have never really understood the best use of field indexing other then setting a field indexing to "Full" increases the speed of finds and look ups at the expense of disk space.  In today's world of inexpensive disk storage is their any reason not to turn indexing on for every field in a solution?

      In a simple invoicing database example with a table for inventory and sales_orders where the field itemNumber is the key field between the 2 tables and sales_orders::quantity, sales_orders::price, and sales_orders::itemDecription are all look-ups from the inventory table what fields are the best to have indexing on?

      Thanks,

      Chris

        • 1. Re: best use of field indexing
          RickWhitelaw
          You're right about inexpensive storage. However, there are many situations, especially involving "Get" functions when "do not store" is the preferred choice. The other side of the coin is that certain "match fields" must be indexed (stored) to work properly. RW
          • 2. Re: best use of field indexing
            philmodjunk

            The other "cost" to having lots of indexed fields is much slower imports should you have to import all your data into a new copy of the file--after a crash corrupts the file for example.

            In most solutions, I'll gladly put up with slower imports to get faster performance during finds and sorts, but as the file gets larger and larger with records exceeding the 100,000 count, I start leaning the other way and start looking for fields that I can turn off or limit indexing without harming performance.

            Note: Here's a trick I figured out for performing faster searches on tables with large numbers of records when one or more fields are not indexed:

            Search in two phases: In Phase 1, enter find mode and specify criteria only in indexed fields. In Phase 2, return to find mode, specify criteria in the unindexed fields and use constrain found set to reduce the found set from Phase 1 to just those matching the criteria specified in Phase 2. This is often vastly quicker than performing a single find with criteria in one or more unindexed field.