Semi-Sorted A database can either be sorted, unsorted or semi-sorted. The first two are pretty obvious, but it is less clear how a database becomes semi-sorted. A database becomes semi-sorted when a new record is added to a currently sorted database and the option to keep records sorted is unchecked. It can also become semi-sorted if a record is edited -- but only if one of the fields used in the sort is edited. Deleting a record will not make a database semi-sorted.
Claris Commercial Few people know that Claris produced a commercial and it aired on national television. It was one of those favor things so it only played. I'm not saying it's anywhere close to the impact of the Apple Super Bowl commercial but it's fun to have a look.
Download the Claris commercial
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 are 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!
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!
Very many thanks.