"No Holding Back FileMaker Blogging"


Navigation:


Support this site by clicking on a sponsor below or becoming a patron!



Create a FileMaker Calendar



Become a patron of this FREE web site!


Recent Blogs:

Currency Formatting
Currency Formatting

Gathering Portals
Gathering Portals

Multiple Choice Picker
Multiple Choice Picker

Popups and Pickers
Popups and Pickers

Window Locking
Window Locking


Meta-Consulting
Meta-consulting is a service Database Pros offers to help other developers over programming hurdles via GoToMeeting. Email John Mark Osborne or call John Mark at (909) 393-4664 to find out more about this service.


Fireside FileMaker Podcast


Quick Tip:

Discontiguous Items
You can select multiple script steps to duplicate, including discontiguous ones. On the Macintosh, hold down the Shift while clicking on script steps to select continuous steps. To select discontiguous script steps, hold down the Command while clicking on steps. Under Windows, hold down the Shift or Ctrl key while clicking. You can move the selected steps, duplicate or delete them. You can even control where duplicated steps are placed. Select an additional step further down in the script and the duplicated steps will be inserted immediately after that step. Just delete the last duplicated step and you have placed your duplicated steps exactly where you desire. This process will also work with just about any dialog presenting a list of items such as fields, layouts and value lists.



Document Management Videos


Fun Stuff:

FileMaker, Inc.
FileMaker, Inc. or FMI is a wholly owned subsidiary of Apple, Inc. It was once named Claris when it also sold MacWrite, MacPaint, Claris Emailer, ClarisWorks, etc. but changed it's name in 1998 when it focused on FileMaker. FileMaker started out as a DOS application called Nutshell but was shaped into the powerhouse it is today by Claris and FMI.



Create a FileMaker Calendar







RSS Feed
The Downside of Indexing
Level: Intermediate
Version: FileMaker 18
Category: General
Tuesday, November 5, 2019
Most people don't think about indexing when designing a solution. FileMaker automatically turns on indexing when needed so why should they? Indexing can only be good, right? I mean, it makes finds faster. Well, in most situations, indexing is nothing to worry about. But, under certain circumstances, indexing can have an adverse effect on your FileMaker file. Let's talk about how indexing works, where it is used and how it can slow down your solution when unchecked.

The Downside of Indexing

What is the Index?
The index is what makes FileMaker searches faster. Imagine trying to locate some keywords in a text book. If you scan each page, it's going to take a long time to find all the instances. If you go to the index of the book, it will have an alphabetical list with page numbers next to each keyword.

That's basically how the index in FileMaker works. Each word in a text field, and the entire value of other fields (e.g. number, date, time, etc.), are placed in alphabetical, chronological or numerical order with record numbers next to each word or value. This makes it much faster for FileMaker to search a database since it's a much shorter list. In fact, it makes searching a thousand record database as fast as a million record database.

The Downside of Indexing

I'm not going to talk much more about how the index is generated since I've already spent an entire article on the subject already. In fact, I'd recommend reading it prior to this article. It's called "In Depth Index" and talks about how the index is created and maintained in FileMaker:

In Depth Index

What Features Use the Index?
As mentioned, the index is most commonly used to make searches faster. However, it is used in many other places. Probably the most well known secondary usage is for relationships. Only the key field at the end of a relationship needs to be indexed. For example, if you have a layout showing records from a COMPANIES table (the start) and a portal showing records from an EMPLOYEES in a portal (the end), the key field in EMPLOYEES only has to be indexed. While the COMPANIES key field doesn't need to be indexed in this scenario, flip the start and end around and a related field showing the company on an employees layout will require indexing on the COMPANIES key field.

The Downside of Indexing

Another notable place where the index is employed is when a value list shows fields from a table. This allows for quick building of popup menus. It's important to note a couple things. First, the index only stores the same word or value (date, time, number, etc.) once so a value list won't show two people with the same name. Secondly, only the field that is displaying needs to be indexed. For example, if you have a popup menu referencing a primary key field (to complete the relationship) and a company field (for identification), the primary key doesn't need to be indexed. Only the field showing in the popup menu has to be indexed.

The Downside of Indexing

FYI: Checking the option to "show values only from second field" not only removes the field in the first column from the popup menu but it also enables indexing to display the value from the field in the second column.

Less notorious uses of the index but just as important to know are auto-complete using existing values and validation by unique or existing value. Despite reports otherwise, sorting does not use the index. There may be a few other straggler features out there that use the index but you get the point. The index is very important for doing things quickly. When FileMaker needs values from a field quickly, it usually turns to the index.

The Downside of Indexing

The Downside of Indexing

What Turns on the Index?
The default setting for FileMaker fields is to turn on indexing when needed. That means, if you perform a search, use a field in a relationship, try to validate for an existing value or create a value list, the index might flip on automatically. In most cases, this is not a concern. Just let FileMaker do it's thing so it's easier to program.

The Downside of Indexing

BTW: Storage options can be found in Manage Database by selecting a field and clicking the Options button.

Turning Off the Index
You can set the index to never turn on if you like. It's pretty easy but is it necessary? It all depends on your file. In general, small and medium files may not have a noticeable size change or speed difference if all unnecessary fields are unindexed. So, what defines a small or medium FileMaker file? That's sixty-four thousand dollar question! If you're too young to remember the show, that was a lot of money back then. My point is, it's hard to determine whether your file will be adversely affected by unnecessary indexes.

There are a lot of factors that go into determining whether an index can increase the size or decrease the speed of a solution. That's right! The index has to be stored somewhere so it takes up space. It also takes a little time to update an index. By itself, one field index won't usually make a difference but combine it with all the other features you are throwing at a solution and the grains of sand start to build up.

For example, you could have a database with a billion records. That would definitely be considered a large database, right? But, what if it only has a handful of fields. Indexing every field shouldn't be an issue. Change that database to a million records, add a hundred fields and toss in a field that contains blog articles and now you have a file that needs a lot of indexing. In this case, it's best to pick and choose which fields need to be indexed and to definitely avoid indexing the article field if possible.

Again, the indexing by itself doesn't cause the entire speed problem. The file bloat that occurs from storing the unnecessary indexes could be caused by indexing alone but not usually a degradation of speed. It's usually an abuse of multiple features that lead to a poorly performing database. For example, you might have a lot of unstored calculations on your layout that exacerbate the indexing requirements into a real speed issue. But, this is just one example out of thousands and the speed issue is likely to be unique to your solution.

The point of all this discussion is that indexing is just one feature that takes time for FileMaker to process. In fact, it's a feature that often gets overlooked so make sure you consider it when troubleshooting a speed issue. But, don't forget all the other features that could cause trouble when abused. It's all about smart programming.

Importing
If we change the scenario slightly and focus in on a specific feature like importing, indexing can be the sole cause of a speed issue. Think about it! FileMaker has to index all those unnecessary fields on import, making it much slower than normal. If you're performing a lot of imports, definitely turn off indexing on fields that don't need it.

However, you also need to consider other features that occur on import like validation and auto-enter. If you have complicated formulas on auto-enter or validation, they could also slow down the import process. It's all about having good troubleshooting skills and being able to isolate the culprit. In this example, you could run a test by turning off auto-enter on import to see if it helps. If it doesn't then you likely isolated the problem to indexing.

Beware
Beware of Quick Find! It will turn indexing on for every field on the current layout with just one find. The only way around this issue is to not use Quick Find or turn off the feature on the fields you don't want searched via the Inspector. I'm personally not a fan of Quick Find and rarely build it into my solutions.

The Downside of Indexing

Loading...
It's also important to note that indexes are loaded on open of a file and cached on the local hard drive. The more and larger indexes, the longer this process will take. If the file is on a portable device with a limited bandwidth and sub par chip set, the load time could be irritatingly slow.

Good or Bad?
I think the conclusion is indexes are both good and bad. In most cases, they speed up finds, relationships and other FileMaker features. In some cases, they may be the feature slowing down your solution, or, at least, part of the problem. My suggestion is to turn off all unnecessary indexes at the conclusion of a project. It doesn't take very long but could save you some serious headaches.

Author:
John Mark Osborne
jmo@filemakerpros.com
www.databasepros.com

This blog is completely free. Please support it by clicking on one of the advertisers at the left side of the window or becoming a patron. Thanks so much!

Comments:

Michael Rocharde 11/07/2019
  Very interesting article with lots of key things to know and understand.
Shawn Krueger 11/05/2019
  John, thanks for the great article. Indexing is definitely one of those things that can be a blessing and curse!

I agree with the need to beware of Quick Find. The problem is, it is one of those user features that is out of the developer's control, unless you disable access to the toolbar… which has other issues. Our usual method is to turn off "Include field for Quick Find" on all objects in the Inspector except one field specifically for searching, which we keep off to the layout margin. This is typically a local (stored) calculated field that is a List ( ) concatenation of all of the fields that we want to be searchable. It's easy to forget to turn off that checkbox, so having View > Show > Quick Find turned on can help. Just eliminate the traffic lights!
Response by:   John Mark Osborne 11/06/2019
Thanks for your comment. I really appreciate it. I love offering multiple sides to an issue. I always hide the Status Toolbar. No exceptions unless it's a database for my own use. Takes up too much screen real estate and doesn't match the rest of my interface. It's also too much work to go through every field on the layout and specify to exclude from Quick Find. But, every developer is different and there's not always a right or wrong way to do things. Again, thanks for your feedback. I'm sure my readers will appreciate your alternative viewpoint. Happy FileMaking!

Add Comment:

First Name: *
Last Name:
Email: *
Web Site:
Comment: *
 Email Addresses will not be shared on the web site!