"No Holding Back FileMaker Blogging"
|
Navigation:
|
Support this site by clicking on a sponsor below or becoming a patron!
Beginner, Intermediate and Advanced Video Training
Become a patron of this FREE web site!
|
|
Recent Blogs:
|
Window Locking
 |
Everything Changes
 |
Subsummary Unique Count
 |
Scripted Change Log
 |
Abstracted Log
 |
|
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.
|
Tips & Tricks Videos
|
Quick Tip:
|
Creeping Labels
Does the information on your labels creep up or down such that the last labels on the page are at the top of the label? The most-likely reason the labels are creeping is due to the Body part being too big or too small. However, let's start with the first row of labels. Does the first row of labels hit exactly where you want it to? If the labels are too low, decrease the size of the Header part. If they are too high, increase the size of the Header part. Once you have your first row printing correctly, move on to the rest of the rows. If they are creeping up, you need to make the Body part bigger. If they are creeping down, you need to make the Body part smaller. When you change the size of the Body part, any change is multiplied over the number of rows of labels you have. Change the Body size as much as the difference in position between the first and second rows of labels and all the labels should adjust correctly. It may take a few tries. |
|
|
Document Management Videos
|
Fun Stuff:
|
Claris and FileMaker CEOs
| Bill Campbell | | 1987 | to | 1995 |
| Guerrino De Luca | | 1995 | to | 1997 |
| Dominique Goupil | | 1997 | to | 2019 |
| Brad Freitag | | 2019 | to | Present |
|
|
|
Create a Complete Contact Manager
|
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.
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.
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.
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.
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.
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.
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.
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!
|
|
|
|
|
