May 12, 2021
FileMaker Tips #2 — To Index or Not to Index?
If you’ve dug into all the field options for FileMaker, you’ve probably come across the indexing options for most fields types under the Storage tab. As stated in its description, indexing can improve performance of finds and relational joins but at the cost of file size and time spent indexing. Let’s break that down.
How does indexing improve FileMaker finds?
If you’ve ever picked up an educational book and flipped to the back you’ll find what they call an “Index” where common topics that are covered in the book are notated with their page numbers as to where you could find more information about them. Now all you have to do to find the pages that talk about “The Mitochondria” is to look at the index and see exactly where to go. Without an index, you’d be scanning all the pages one by one trying to find any mention of it.
FileMaker’s indexing feature is just the same. It indexes field values and keeps track of what records they can be found in. So when you pull up Find Mode and search for “John Doe” in the name field, it can quickly pull you to all the records that contain “John Doe” in that field extremely fast. You’ll probably not be able to tell the difference in a smaller database, but try indexing a field in a half-million record database, and you’ll see how much an indexed field can make a difference.
What do the three options do for indexing?
There are three settings for indexing a field. By default, when you create a new field, indexing is set to None, with the option to Automatically create indexes as needed is enabled. The fields that can be indexed are number, text, calculations (stored), date, and timestamp.
Before we dig into the different options, let’s first address that there are two types of indexing. Word and Value. The word index will take every word that is within the field and index that word. Value will index the entire value of the field. Text fields are the only fields that can have a word index, so only text fields will allow you to specify either Minimal or All. In the screenshot above, we are looking at the index options for a number field. Note how only None or All is available for this field type.
- None is pretty self-explanatory. No index will be created for the field, unless the Automatically create indexes as needed setting is enabled. I will touch on this setting a bit more later.
- Minimal means that only the value index will be created for the field. This only applies to the text fields since there can be a word or value index.
- All means that both the word index and value index will be created for the field.
Pro Tip: Consider whether you’ll need to have both the word and value index enabled on text fields or just the value index. Enabling both indexes for a text field will mean extra indexing and increase your file size regardless of whether you use those indexes for finds. More on this below.
What is “time spent indexing?”
If you read closely you probably noticed that there is an additional negative to indexing aside from a larger file size, time spent indexing. Time spent indexing occurs when you’ve allowed indexing of the field automatically or have turned on the indexing for the field. In order to create an index, FileMaker has to index every record in the database to gather the values or words for that field. This is the time spent indexing.
How to see this in practice:
- Grab a database that has a pretty heavy table of records.
- Take a field you already have indexed. If you look at the field definitions for a table it should say “indexed” under its options.
- Turn off the indexing to clear out the indexing for that field by setting it None.
- Save your changes.
- Return to the field and enable Create indexes as needed.
- Save your changes.
- Perform a find on the field.
- Perform a second find on the field.
You should see a difference in the performance of the first find compared to the second. This is because the first find is running through and creating an index of the field since we turned on the option to “Create indexes as needed.” Upon running another find on the field, it now has an index setup and stored for the field that it can reference to locate all the relevant records for your query.
How do I optimize indexing of my solution?
If you’ve never really played around with the indexing options of your database, you’re probably indexing more than you need to index since fields will be set to create indexes automatically.
If you host your FileMaker solutions, the indexes must be downloaded to FileMaker Pro and stored in the local cache. You may notice a delay in loading a layout or scrolling through records. Additionally, when edits are made to records with indexed fields, those fields have to be indexed again. You may have gotten that nice progress dialog when doing a Replace Field Contents on a found set of records.
Smaller databases may not see much benefit in optimizing the indexing, but I would suggest considering modifying your index settings on databases with large sets of records and/or numerous fields to a table.
Optimizing fields that don’t need to be indexed
Since FileMaker will default to None and Create indexes as needed, you should go through your fields that aren’t benefiting from indexing and disable the Create indexes as needed option.
Optimizing text fields
Since we have two options for text fields, Minimal and All, there are cases where we might want one option over the other.
For fields that contain more than one word such as notes, addresses, or descriptions, we will want to use All so that we index the words for partial finds.
For fields that are used for relationship connections, use Minimal.
For all other fields, use Minimal if there is a possibility that it will be used in a find.
Pro-Tip: Indexing is required for relationships. If you turn off indexing on a field used in a relationship, you will not be able to view the related records from that relationship. Make sure you don’t turn off indexing for these fields.
Quick Finds
If you look in the top right of FileMaker Pro, you’ll see a search box. This is the Quick Find search.
Quick Finds is a feature that searches all fields on the layout that has Quick Find enabled. This is very powerful for general searches. However, with a database with unoptimized indexing options, you may ultimately create more indexes than you ever need when you perform a find with it.
The Quick Find settings can be found in the Layout Setup dialog and the Inspector (for specific fields).
If you want to leave a layout enabled for Quick Find, but want to control which fields don’t apply to the Quick Find, you can go to the Data tab in the Inspector and disable this checkbox.
It is recommended to turn off the Quick Find entirely for script-use layouts and developer layouts so that unintentional indexing doesn’t occur during scripting or development.
Insert from Index (Ctrl + I)
A somewhat forgotten feature in FileMaker is Insert from Index. For fields that have indexing, you’ll find an option under the Insert Menu that allows you to pull up the View Index dialog for a field on the layout.
This dialog shows you all index entries that have been created for a field. In the example above, we have a Username field with two usernames that have been indexed. Above we are seeing the Value Index of the field.
Because this is a text field, we have the option to Show individual words as well. If you were to open this dialog with a number field, you would not see this option.
As you can see, when you toggle Show individual words, it gives you the Word Index.
With this dialog, you can paste and indexed value/word into the field.
Final Words
I know this article is a bit lengthy so from here I will let you explore the index options with your expanded knowledge of FileMaker indexing. With how unique each FileMaker solution may be, it will be up to you as the developer to determine what fields should be indexed and which ones can be disabled from indexing.
Using the suggestions above, you can work to optimize your solution’s indexing in the following ways:
- Turn off indexing for fields that don’t need to be indexed.
- Turn on Minimal indexing for text relationships and for most text fields that will not have multiple words entered in.
- Adjust Quick Find settings to ensure that you or a user don’t unintentionally force a full indexing of an entire table.
Want Me to Cover a Specific Topic of FileMaker Development?
Comment below or email me at jeff@jeffreyhenry.me to make a request. If its worth writing a blog about, I will write one up or record a video to cover it!
Feel free to contact me if you need help with FileMaker Development, as well.
As always Happy Coding!
