Level: Intermediate Category: Scripting Tuesday, February 14, 2017
Indexes make finds fast! But, indexes also support a bevy of other features such as relationships, value lists and validation. This blog entry will explore the definition of an index from a FileMaker point of view as well how it is used throughout the application.
The best way to define indexing is by way of analogy. Let’s say you're reading a technical handbook on FileMaker and want to find the sections mentioning global fields. You could flip through each page scanning for the word “global” or save yourself a ton of time and visit the index of the book. The index will list all the pages where "global" can be found. This is exactly how a word index functions in FileMaker. Instead of searching every single record, each unique word is added to the index with references to every record where it occurs. This makes searching incredibly fast since it can now search through a much smaller list of words.
You can quickly view the index of a field by clicking into it and choosing From Index... from the Insert menu. There are actually two types of indexes possible for a text field. The first is represented when the "show individual words" option is unchecked. It shows how the index looks for a text field when a find is performed.
When the option is unchecked, the index is displayed as a relationship would view it. Relationships consider the entire entry for the match so word separators are ignored. The only exception is a return character but multi-keys are too advanced to cover at this point.
The index for a date, number, time or timestamp field doesn't have the option for "show individual words" since these field types can only be searched as a complete value.
To confuse matters even more, FileMaker defines indexing in Manage Database as Minimal and All. Minimal means only one index type has been created while All means two index types have been created. The two types of indexes are Word and Value. A Word index is used for finds on a text field while a Value index is used for searches on number, date, time and timestamp fields, relationships and validation. Only text fields can have both index types since only text fields can be searched by word (Word index) and be used in a relationship (Value index). Up to 100 characters of each word or value are indexed.
Words are defined most often by spaces but can also be determined by commas, periods, colons and other characters. It’s best to test the index by entering values into a field and viewing it, in order to gain a better understanding. For example, "john.osborne" is considered one word and is indexed as such. Add a space after the period and FileMaker now considers the period as the end of a sentence and indexes "john" and "osborne" separately without the period.
A Value index is used for searching number, time and date fields as well as relationship key field matching. Up to 100 characters of each return-separated value are indexed (returns are used to create multi-keys but won't be discussed here). While searching can be done on partial text in a Word index, only complete values can be searched for in a Value index. When you think about it, why would you want to search for a partial number or date. In addition, Value indexes are used for relationships to match parent and child on the entire value. While relationships are bidirectional, if you are only use the relationship from one point-of-view, only the match field from the ending point needs to be indexed. In other words, when adding a portal to a layout, only the key field on the portal side needs to be indexed.
In the screen shot below, FileMaker automatically turned on the index for the "notes" text field a find was performed due to the option to "automatically create indexes when needed". The Minimal option was utilized since only a Word index is required to search a text field.
As soon as a feature requiring a Value index is setup for a field, like a relationship, the "All" option is automatically changed, as long as the option "automatically create indexes when needed" has not been unchecked.
The next screen shot shows a date field. Notice the "Minimal" option is dimmed since number, date, time and timestamp fields can't have Word indexes. Searching and relationships are both based on the Value index.
One thing you should notice about a word index is that it does not differentiate between uppercase and lowercase. Searching for “FRED” also finds “fred”, and vice versa, unless you change the language to Unicode. Unicode is commonly employed as an indexing type to enable finds on email addresses since the at sign (@) is a reserved word. As long as the email addresses are all in the same case, this works perfectly. If you are grabbing email addresses from the internet or emails sent to you, people often use uppercase cause it doesn’t matter to the internet. If you turn on Unicode indexing, you may be able to search on full email addresses but you’ll have to get the case correct. I would suggest using standard indexing al the time and simply train users how to search for email addresses and other tricky search criteria using find criteria surrounded by quotes (e.g. "firstname.lastname@example.org").
Indexes are also utilized in a variety of other features such as value lists based on the contents of a field. This enables FileMaker to quickly build a list for a pop-up menu based on the contents of a field across thousands of records. The downside is that only unique values are placed in the value list simply because that is how an index functions. In most cases this doesn’t cause a problem but consider yourself warned. Indexes are also used in the validation option to determine uniqueness or existence but not many other places. A common misconception is that sorts use the index but this is simply not true.
The downside of indexing is speed and size. It takes time to create an index and space to store it. One index isn’t going to be noticeable but if you index every field, you may have speed issues or file bloat, especially if you index a notes field. It’s important to turn off the indexing for fields that will never be searched by unchecking the option to “automatically create indexes as needed”.
In addition, prevent access to these fields during search by turning off the Inspector option to allow access in find mode. You can even hide fields with the Hide Object feature but I’m getting ahead of myself. The key here is turning off indexes once the development process is over. While you may search fields during the development process, turn off indexing as needed when the solution is deployed.
This blog is completely free. Please support it by clicking on one of the advertisers at the left side of the window. Thanks so much!
Thank you for the helpful response - I'm glad you have the right connections with the FileMaker engine head developers!
I found this blog posting via Twitter and am really glad I found it. The link just indicated it was about indexing and I was surprised & thankful to discover it was your blog. I just was made aware recently that I didn't understand how indexes work. Your explanation was very helpful.
Can you affirm that any existing indexes (created during development) are deleted when you change indexing to "None" in field definitions? (I went to test it with "Insert from Index" & because I had the option on to create it automatically, it seems to have been re-created again. If I had the option to create it automatically turned off, however, the option to "Insert from Index" was no longer available, but that doesn't mean the previously created index was deleted.)
Glad you enjoyed the article. I asked one of the head developers for the FileMaker engine and here is exactly what he said: "Yes, it will delete the index. Make sure to turn off “create index if needed” if you want to make sure it never comes back. Also, after a big delete like that there may still be quite of few partial pages laying around so if you really want to squeeze out all the free areas you should also use “Save a Copy As” with the “compacted copy (smaller)” option. One more note, FileMaker files never shrink in size until they are closed. So if they delete the index and don’t close the file when you look at it in the Explorer/Finder, it won’t change in size until the file is closed."
Wow. I'll be honest, although I've been developing in Filemaker for a long time, I had the suspicion I did not really grasp how indexing really works and 'To Index, Or Not to Index, That Is The Question...'.
This succinct, brilliant elucidation now fills that gap for me. Am looking forward to mining gold on JMO's blog!