SQLite - INDEXED BY
The "INDEXED BY index-name" clause specifies that the named index must be used in order to look up values on the preceding table.
If 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 shall be used when accessing the preceding table, including implied indices create by UNIQUE and PRIMARY KEY constraints.
However, the INTEGER PRIMARY KEY can still be used to look up entries even when "NOT INDEXED" is specified.
Following is the syntax for INDEXED BY clause and it can be used with DELETE, UPDATE or SELECT statement:
SELECT|DELETE|UPDATE column1, column2... INDEXED BY (index_name) table_name WHERE (CONDITION);
Consider table COMPANY we will create an index and use it for performing INDEXED BY operation.
sqlite> CREATE INDEX salary_index ON COMPANY(salary); sqlite>
Now selecting the data from table COMPANY you can use INDEXED BY clause as follows:
sqlite> SELECT * FROM COMPANY INDEXED BY salary_index WHERE salary > 5000;
Kindly note that though SQLite specification talks about the above-mentioned Syntax for INDEXED BY clause but I tried all the way to make INDEXED BY work on my installation but it did not work. If you found a solution kindly share it at email@example.com.