SQLite - INDEXED BY Clause

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 created 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.

Syntax

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);

Example

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;

This will produce the following result.

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
7           James       24          Houston     10000.0
2           Allen       25          Texas       15000.0
1           Paul        32          California  20000.0
3           Teddy       23          Norway      20000.0
6           Kim         22          South-Hall  45000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0