SQLite Indexed By
"INDEXED BY index-name" clause provisions need to be named in the index to find the value in the preceding table.
If the index name index-name does not exist or can not be used to query, then SQLite statement is preparing to fail.
"NOT INDEXED" clause specifies when accessing the preceding table (including implicit index by the UNIQUE and PRIMARY KEY constraints that were created), no use of the index.
However, even if you specify "NOT INDEXED", INTEGER PRIMARY KEY can still be used to find entries.
Here is the syntax INDEXED BY clause, it can be used with DELETE, UPDATE, or SELECT statement:
SELECT | DELETE | UPDATE column1, column2 ... INDEXED BY (index_name) table_name WHERE (CONDITION);
Suppose table COMPANY, we will create an index, and use it INDEXED BY operations.
sqlite> CREATE INDEX salary_index ON COMPANY (salary); sqlite>
Now use the INDEXED BY clause to select data from a table COMPANY, as follows:
sqlite> SELECT * FROM COMPANY INDEXED BY salary_index WHERE salary> 5000;