If you are experienced in developing in FileMaker, you know you can create indexes, which are lists of the words or values in a field. FileMaker Pro uses indexes for searching and for joining related tables. Indexes increase the speed of searches as well as support other features like sorting, using value lists and field validation.
Picture the index at the back of a big textbook. When searching for a specific topic, you find it easily in the index at the back of the book, where it includes a list of page numbers that topic appears on. This way, the user searches maybe a handful of pages rather than hundreds or even thousands of pages. This is the best analogy for how indexing works in FileMaker, it stores small portions of data to act as references so that certain tasks can perform faster.
Word indexes
FileMaker Pro uses two different types of indexes: word indexes and value indexes. Word indexes can only be created for text fields and calculation fields that return a text result, where they are used for searches. A word index is created by storing each unique word in a field. Then, FileMaker adds each unique word to the index with references to each record occurrence, making searching way faster.
As nice as that sounds, you must be aware that too much indexing can be a bad thing. Fields containing large amounts of text data, will generate large indexes, which can vastly increase file size. Keep that in mind with fields that could potentially store large amounts of text data. Just consider:
- What the data will be?
- How much will it be?
- Will users be searching records using this field?
Value indexes
The other type of indexes is called a value index and is used for searching numeric data, which also includes date and time fields, along with key field mating in relationships. A value index is created by taking each line of text, delimited by the carriage return character. Value indexes can only be searched with complete values; thus, match fields should be indexed on the side of the foreign key. To optimize performance on your database you will need to understand where you need indexing at all, and how to determine which type of indexing you need, if so.
Inside the field options, the three indexing possibilities are shown. ‘None’ for no indexing, ‘Minimal’ to use value indexing, and ‘All’ to use both word and value indexes. For text fields, selecting ‘All’ will tell FileMaker to create BOTH word and value indexes. For numeric data (number, date, time, and timestamp fields), selecting ‘All’ will create an index of all of the field’s values.
Below is the result of viewing the index of a field with “All” indexing. This can be done by highlighting a field's contents and choosing Insert > From Index ( Command+i ). Since this field has both word and value indexing you will see the difference shown in my screenshots below.
The image on the left shows a value index, so it takes the entire line of the field’s data. The field on the right shows the word index so you can see that 2 word first names will not be viewed as such. In the photo on the right, the name “Anne” would appear alphabetically in the list whether or not there was another “Anne” in the system.
Automatically create indexes as needed
An additional critical option you see in the Indexing portion of the field options is the checkbox for Automatically create indexes as needed.
This tells FileMaker to create the index only if a user performs a Find on the field or if the field is used as a match field in a relationship. As I mentioned in the Word Indexes section. The problem with too much indexing is it increases the file size and impairs performance speed. It is crucial to remember to turn off indexing for fields that could have large amounts of data and will not be searched on by deselecting this box.
Hopefully, you now have an idea of how indexes can help your database perform faster, and also understand the risk of too much indexing. When you understand where to and where not to store indexes, you will be able to confidently optimize your solutions.
About the Author
