ODBC and JDBC Guide

Chapter 7 | Supported standards 45
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 ON table_name.column_name
CREATE INDEX 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.
FileMaker automatically creates indexes as needed. Using CREATE INDEX causes the index to
be built immediately rather than on demand.
Example
CREATE INDEX 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
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)