ODBC and JDBC Developer’s Guide

30 FileMaker ODBC and JDBC Developer’s 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
FROM clause
The FROM clause indicates the tables that will be used in the SELECT statement. The format is:
FROM table_names [table_alias]
table_names can be one or more simple table names in the current working directory or complete pathnames.
table_alias can be used to give the table a more descriptive name, or to abbreviate a longer table name.
Field names can be prefixed with the table name or the table alias. For example, given the table specification
FROM employee E, you can refer to the LAST_NAME field as E.LAST_NAME. Table aliases must be used
if the SELECT statement joins a table to itself. For example:
SELECT * FROM employee E, employee F WHERE E.manager_id = F.employee_id
The equal sign (=) includes only matching rows in the results.