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.

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.