You may have heard these complaints before: “This was working great when I opened the file locally, but now that it’s hosted by a FileMaker Server, it’s kind of slow.” Or, “This works acceptably for my LAN users, but I have some users connecting from outside the office, and it’s slow for them.” If your database is exhibiting these symptoms, it could be Indexes slowing your database down.
What are indexes for?
FileMaker Pro creates indexes of the data in fields that have indexing turned on. When performing a Find on an indexed field, FileMaker will search the index instead of the actual field. Finds are vastly accelerated by this. In addition, relationships in FileMaker are based on indexed fields. More on this in a bit.
Don’t index everything
While indexes are beneficial, it is possible to have too many indexes. When connecting to a hosted FileMaker file, the indexes must be downloaded to FileMaker Pro and stored in its local cache where they can then be called on as needed. In some cases this can add noticeable lag to accessing a layout, or scrolling through records. In addition, when editing a record, FileMaker Pro has to update the indexes. If a user or a script performs a replace on a large set of records, the time spent updating indexes can be noticeable. Index optimization may not be necessary at all on a small database. However, a database that has many fields or many records will benefit from optimization.
How to optimize
By default, field indexing is set to “None” and “Automatically create indexes as needed.” With these settings, indexes will be created as soon as a Find is performed on that field. Instead of leaving the defaults on all fields, consider setting all fields to “None” and unchecking “Automatically create indexes”. Then turn on indexing only for fields that need to be searched, and key fields.
Field Indexing Settings
For text fields, there is a choice of “Minimal” and “All”. For fields used to categorize, such as a type field with a value list of “Individual” and “Group”, choose “Minimal”. For fields with longer strings of words, like “Street Address”, choose “All”. For text fields used in relationships, set the index to “Minimal”. All fields used in relationships (key fields) must have indexing turned on, or you will not be able to view related records in a portal or use the Go To Related Record script step. Remember, don’t turn on indexing at all for non-key fields unless you think it’s necessary.
Quick Find
Quick Find is a FileMaker 11 feature that searches all fields on a layout that has Quick Find enabled. By default, Quick Find is enabled at the layout level, and all fields also have Quick Find enabled. The default settings for Indexing and Quick Find can result in one Quick Find creating indexes for all the fields on the layout! Therefore, consider turning off Quick Find entirely for some layouts, such as developer layouts, in Layout Setup.
You can control Quick Find for the whole layout in the Layout Setup dialog.
For user layouts, take a look at how FileMaker indicates the Quick Find status of the fields.
In Layout Mode, the magnifying glass indicates that QuickFind is enabled on a field.
In our experience, Quick Find is very slow when it has to search those “yellow” fields, so turning off Quick Find on those fields will not only avoid unnecessary indexes, but will keep the “quick” in Quick Find. To do this, just uncheck “Include field for Quick Find” on the Data tab of the Inspector, under Behavior.
Turn Quick Find on and off per field using the Data tab of the Inspector.
Subscribe to our blog feed