Defining field indexing options

You can create indexes, which are lists of the words or values in a field. FileMaker Pro uses indexes for searching and for joining related tables. Indexes increase the speed of searches but also increase file size.

FileMaker Pro uses different indexes for different tasks:

  • Value indexes can be created for text, number, date, time, and timestamp fields, as well as calculation fields that return results of these same types. Value indexes are used for joining related records and for searches in number, date, time, and timestamp fields, and calculation fields that return results of these same types.

    Note  Some behavior is dependent upon the data source or drivers when using Japanese collation that does not distinguish katakana or hiragana, either in full- or half-width. This is also the case with roman characters with accents.

  • Word indexes can only be created for text fields and calculation fields that return a text result, where they are used for searches. A word index is created by storing each unique word in a field. Fields containing large amounts of text can generate large indexes, as each unique word in the text field appears in the word index. This can significantly increase file size.

To set indexing options for a field:

  1. Choose File menu > Manage > Database.
  2. Click the Fields tab.
  3. If your database contains more than one table, select the appropriate table from the Table list.
  4. Click the field name.
  5. Click Options (or double-click the field name).
  6. In the Options for Field dialog box, click the Storage tab.

    If you selected a calculation field, you see the Specify Calculation dialog box. Click Storage Options.

  7. Select indexing options for the field.

    For normal use, use None or Minimal and select Automatically create indexes as needed.

    8. Select

    To

    None

    Prevent FileMaker Pro from indexing the field.

    Minimal

    Create a value index of a text field’s contents or a calculation field returning text results.

    All

    Create both word and value indexes for text fields or calculation fields returning text results. For number, date, time, and timestamp fields, as well as calculation fields returning results of these types, All creates an index of a field's values.

    Automatically create indexes as needed

    Create the necessary index only if a user performs a search using the field or if the field is used as a match field in a relationship.

    A language from the Default language list

    Specify the language used for indexing and sorting values in a text field. Each language is tailored to support language-specific indexing and sorting requirements.
  8. For calculation fields, select Do not store calculation results if you want FileMaker Pro to calculate the result only when needed, then click OK.
  9. Click OK.

Notes 

  • A value index is created by taking each line of text (delimited by the carriage return character). See Choosing a language for indexing or sorting.
  • Stored results require more disk space. Unstored results require more time to calculate.
  • Selecting All for text fields can significantly increase file size, as every word in the text field is indexed. Certain operations, such as importing records, may also take more time, as each word in the field is added to the field’s index as the import occurs.
  • Automatically create indexes as needed indexes the field the first time a user performs a find request (searches) on the field. The first search is slow because the index is being created. However, subsequent searches on that field are faster because they use the index. (This option also creates an index when the field is used in a relationship.)
  • To create relationships using text fields as match fields without creating word indexes for these fields, use Minimal and deselect Automatically create indexes as needed.
  • The combination of selecting None and deselecting Automatically create indexes as needed prevents the field from being used to create relationships.

Related topics