36 FileMaker ODBC and JDBC Guide
CREATE INDEX statement
Use the CREATE INDEX statement to speed searches in your database file. The format of the CREATE
INDEX statement is:
CREATE INDEX [ index_name ][ON] table_name.column_name
CREATE INDEX is supported for a single column (multi-column indexes are not supported). Indexes are
not allowed on columns that correspond to container field types, summary fields, fields that have the global
storage option, or unstored calculation fields in a FileMaker database file.
Creating an index for a text column automatically selects the Storage Option of Minimal in Indexing for the
corresponding field in the FileMaker database file. Creating an index for a non-text column (or a column
formatted as Japanese text) automatically selects the Storage Option of All in
Indexing for the corresponding
field in the FileMaker database file.
Creating an index for any column automatically selects the Storage Option of Automatically create indexes as
needed in Indexing for the corresponding field in the FileMaker database file.
Example
CREATE INDEX myIndex ON Salespeople.Salesperson_ID
DROP INDEX statement
Use the DROP INDEX statement to remove an index from a database file. The format of the DROP INDEX
statement is:
DROP INDEX [ON] table_name.column_name
Remove an index when your database file is too large, or you don’t often use a field in queries.
If your queries are experiencing poor performance, and you’re working with an extremely large FileMaker
database file with many indexed text fields, consider dropping the indexes from some fields. Also consider
dropping the indexes from fields that you rarely use in SELECT statements.
Dropping an index for any column automatically selects the Storage Option of None and clears Automatically
create indexes as needed in Indexing for the corresponding field in the FileMaker database file.
The PREVENT INDEX CREATION attribute is not supported.
Example
DROP INDEX ON Salespeople.Salesperson_ID
SQL aggregate functions
Aggregate functions return a single value from a set of records. You can use an aggregate function as part
of a SELECT statement, with a field name (for example, AVG(SALARY)), or in combination with a column
expression (for example, AVG(SALARY * 1.07)).
You can precede the column expression with the DISTINCT operator to eliminate duplicate values. For
example:
COUNT (DISTINCT last_name)
In this example, only unique last name values are counted.
Important Use uppercase letters for SQL function names (some are case sensitive).
