May 16, 2021 SQLite
The "INDEXED BY index-name" clause states that a named index must be required to find the values in the previous table.
If the index name index-name does not exist or cannot be used for queries, then the preparation of the SQLite statement fails.
The "NOT INDEXED" clause states that the index is not used when accessing the previous 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 find entries.
Here 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);
Assuming there is a table COMPANY, we will create an index and use it for INDEXED BY operations.
sqlite> CREATE INDEX salary_index ON COMPANY(salary); sqlite>
Now use the INDEXED BY clause to select the data from table COMPANY, as follows:
sqlite> SELECT * FROM COMPANY INDEXED BY salary_index WHERE salary > 5000;