Updates to “Constrain Found Set” in FileMaker 21.1

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.

Constrain Found Set script step in FileMaker 21.1

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.

5 thoughts on “Updates to “Constrain Found Set” in FileMaker 21.1”

  1. 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?

    1. Yes, performance may decrease in most cases. It is better to enable it only when your tests indicate performance improvements.

      1. Shawn A. Krueger

        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.

      2. 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 ?

        1. Here’s the official information about this new option:

          Constrain Found Set documentation

          Find without indexes doesn’t use the indexes of criteria fields. If the criteria fields are indexed and the current found set is already a small subset of the total number of records in the table, then this option may improve the performance of a constrained find.

          FileMaker Pro release notes

          To improve the performance of some find requests that use the Constrain Found Set script step and use criteria fields that are indexed, you can use the new Find without indexes option. This option may be particularly helpful for constrained finds that operate on found sets that are already a small subset of the total number of records in the table.

          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.

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top