SQLite Indexed By

The "INDEXED BY index-name" clause specifies that the named index must be used to look up values in the preceding table.

If the index name index-name does not exist or cannot be used for the query, then the preparation of the SQLite statement fails.

The "NOT INDEXED" clause specifies that no index is used when accessing the preceding table (including implicit indexes created by UNIQUE and PRIMARY KEY constraints).

However, even if "NOT INDEXED" is specified, INTEGER PRIMARY KEY can still be used to look up entries.

Syntax

The following is the syntax of the INDEXED BY clause, which can be used with DELETE, UPDATE, or SELECT statements:

SELECT|DELETE|UPDATE column1, column2...
INDEXED BY (index_name)
table_name
WHERE (CONDITION);

Example

Assume there is a table COMPANY, we will create an index and use it to perform an INDEXED BY operation.

sqlite> CREATE INDEX salary_index ON COMPANY(salary);
sqlite>

Now use the INDEXED BY clause to select data from the table COMPANY, as shown below:

sqlite> SELECT * FROM COMPANY INDEXED BY salary_index WHERE salary > 5000;
Other Extensions