- INDEXING
What effect does indexing have on Performance? Why would you want to reduce it? How can you design to reduce indexing?
When FileMaker builds an index it creates and adds a resource, data, to the .fmp12 file.
Such indexes are essential for some aspects of FileMaker use:
- relationships
- value lists
- perform find
- [are there any other use cases where indexing is essential?]
[2023-04-19: I removed the ref to sorting - thanks to FabriceN for correcting this point. NL]
The ease of use default setting is that every field that is added to a table, if it is indexable, is set to index if required. If Find Mode access is permitted and the fields contain much data then there are several performance hits:
A. indexing of big content on many fields may take time and hence slow down system response whenever FileMaker decides to reindex or update the index of a field
B. indexing makes a file bigger and hence creates additional load on hosting and backup
C. anyone know whether indexing performance differs over WAN i.e. can it now run server side or does it engage the client as part of the process?
D. [other possible performance hits?]
If performance is your priority then it makes sense to only index what is essential and not to index anything that is not essential. Just ask the machine to do less.
The are two different types of indexing and it makes sense to understand the difference.
Would someone like to explain the difference between full and reduced indexing?
Nick
I believe that record sorting may fall into this list, as well.
I don’t think so. At least a long time ago Ray Cologon demonstrated that indexing had no impact on the sort speed. It may have evolved since then but I don’t recall any announcement on the subject.
1 Like
Indexing is also much faster to spread around the users.
In solutions with many users accessing the same records with a high probability, Open record might fail to detect that a record is already locked by another user (the database cache is not yet updated), so a work around is to create a record in a single field table with the primary key of the opened record. A value list (index of that field) will spread much faster and can be checked with ValueListItems.