Indexes in SQLite

SQLite uses B-tree indexes, just as many other relational databases.

You declare an index outside the table declaration like this:

CREATE [UNIQUE] INDEX index_name
  ON table_name(column_list);

You can list the indexes for a table in the SQLite command-line program by using the .indices shell command. Here's example:

sqlite> .indices foods

You can also obtain this information by querying the sqlite_master table.

Non-UNIQUE Indexes

You can indeed define a non-unique index on any column or set of Columns. Unlike a PRIMARY or Unique Key, a non-unique index does not validate each row for a unique value but allows different rows to have the same value in the column or set of columns that are part of the index.

Index Utilization

It is important to understand when indexes are used and when they aren't. There are very specific conditions in which SQLite will decide to use an index. SQLite will use a single column index, if available, for the following expressions in the WHERE clause:

column {=|>|>=|<=|<} expression
expression {=|>|>=|<=|<} column
column IN (expression-list)
column IN (subquery)

Multicolumn indexes have more specific conditions before they are used. This is perhaps best illustrated by example. Say you have a table defined as follows:

create table foo (a,b,c,d);

Furthermore, you create a multicolumn index as follows:

create index foo_idx on foo (a,b,c,d);

The columns of foo_idx can be used only sequentially from left to right. That is, in the following query:

select * from foo where a=1 and b=2 and d=3

only the first and second conditions will use the index. The reason the third condition was excluded is because there was no condition that used c to bridge the gap to d. Basically, when SQLite uses a multicolumn index, it works from left to right column-wise. It starts with the left column and looks for a condition using that column. It moves to the second column, and so on. It continues until either it fails to find a valid condition in the WHERE clause that uses it or there are no more columns in the index to use.

But there is one more requirement. SQLite will use a multicolumn index only if all of the conditions use either the equality (=) or IN operator for all index columns except for the rightmost index column. For that column, you can specify up to two inequalities to define its upper and lower bounds. Consider this example:

select * from foo where a>1 and b=2 and c=3 and d=4

SQLite will only do an index scan on column a. The a>1 expression becomes the rightmost index column because it uses the inequality. All columns after it are not eligible to be used as a result. Similarly, the following:

select * from foo where a=1 and b>2 and c=3 and d=4

will use the index columns a and b and stop there as b>2 becomes the rightmost index term by its use of an inequality operator.