FileMaker 2024 (i.e. FileMaker 21.0), was released on June 4, 2024. Learn more about FileMaker 2024 on the Claris website. If you’re exploring a new solution on the Claris platform, we encourage you to sign up for a free trial here.
A new option was added to the “Constrain Found Set” script step in FileMaker 21.1 that forces the database query to ignore indexes. This impacts some cases where constrained finds are faster if run without involving indexes.
You are most likely to encounter these situations when the total record count of the table you’re doing the constrained find on is more than a million records and the found set right before the constraint is very small, such as fifteen records. Using the Constrain Found Set script step is beneficial in these scenarios as it allows for a more efficient refinement of the smaller subset of records. This efficiency is achieved by bypassing the need to traverse indexes. Remember that when FileMaker creates an index, it includes every record in the table that contains a value for that field.
Previous methods of avoiding indexes in such scenarios included removing indexing on the queried fields or creating unstored calculation fields (never indexed) and then querying these unstored calculation fields, thereby skipping field indexes.
In our tests, the performance difference was not noticeable in most cases, but in some, “Find Without Indexes” sped up the Constrain Found Set step by more than 150 times.
Please use this step carefully and document the speed test results so that you can review them in future FileMaker releases to ensure that the decision to avoid indexes is still appropriate in the long run.
What does “Please use this step carefully…” mean? This wording seems to imply that performance might decrease if using “Find without indexes”. Is that the case?
Yes, performance may decrease in most cases. It is better to enable it only when your tests indicate performance improvements.
I would probably build in some branching for when you want to use this. Something like:
If Get ( TotalRecordCount ) > 99999 and Get ( FoundCount ) < 100
Do the Constrain without indexes
Else
Do the Constrain with indexes
End If
Tweak those numbers, based on your speed tests, of course.
I suppose it could also vary by which field you are searching.
For performance to improve rather than decrease when using “Find without indexes”, the ratio of the total record count in the table to the starting found set count must meet a certain threshold ?
Here’s the official information about this new option:
Constrain Found Set documentation
FileMaker Pro release notes
Performance improvements with the “Find without indexes” option are more likely when the found set is a small subset of the total records. The ratio of the found set size to the total record count plays a role, but it’s not a strict threshold—testing in your specific context is recommended.