YouTip

# 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 named index-name does not exist or cannot be used for the query, then the SQLite statement preparation 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, the INTEGER PRIMARY KEY can still be used to look up entries. ## Syntax Here is the syntax for 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 for 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 follows: sqlite> SELECT * FROM COMPANY INDEXED BY salary_index WHERE salary > 5000;