1 Reply Latest reply on Jul 12, 2012 4:32 PM by philmodjunk

    Indexed Calculations vs. Unstored Calcs

    JimMac

      Title

      Indexed Calculations vs. Unstored Calcs

      Post

      How does FMP or the programer decide whether a Calcualtion Field type is Indexed, Unstored, or ""?  I see this designation on my Tables Fields tab under the Options line.?

      What is biggest advantage to Indexed vs Unstored, in a database design?

      Jim....

        • 1. Re: Indexed Calculations vs. Unstored Calcs
          philmodjunk

          Think of an index just like the index to a book. It's a datastructure (probably a b-tree) of all values entered in that field--each value exists in the index only once and just like a book's index, there are pointers with each value that identify the records where that value is found. In a book, you'd use an index to find all pages where a word is used and and index in a dataase is used in much the same way, to find records. Specifically in searches, sorts and on the "many" or "child" side of a relationship.

          When you specify indexed storage for a field, FileMaker has to spend resources keeping this data structure up to date. Interestingly, adding a new value, changing the value in a single field, deleting a record all seem to execute very quickly even in large fields but you will see an impact on performance in several key areas:

          If you perform a search with criteria entered into an indexed field, fileMaker pops up the found set pretty quickly--even with large numbers of records, though the time taken is a geometrical function of the number of records so larger data sets will take longer to search, but if you specify criteria in an unidexed field, there can be a very significant delay when searching large data sets as FileMaker first creates an index for that field and then uses it in the query to pull up your records. If you had specified indexing as needed and this was the first search on that field, the next search with criteria in the field will go much faster as it now has an index. If you specified do not index, you are back to rebuilding an index...

          But the pendulum swings the other way when you import data into the table. Any field that is indexed has to have it's index created as part of the import process so the number of indexed fields can greatly increase the time it takes to import data. In fact, one of the ways to rebuild all the indexes in a file is to import all your data into a clone of the file. The size of your file will also grow more quickly, the more indexed fields are defined in your table. That data structure has to be stored after all.

          Thus, in small databases with only a few thousand records, you often can just index everything that can be indexed and everything works well. But as your tables amass more and more data, it becomes important to review the fields defined in your tables, and adjust the indexing options to reflect the way that particular field is used in your database.