SQLite: Indexes

This SQLite post explains how to create, drop, and rename indexes in SQLite with syntax and examples.

What is an Index in SQLite?

An index is a performance-tuning method of allowing faster retrieval of records. An index creates an entry for each value that appears in the indexed columns. Each index name must be unique in the database.

Create an Index

You can create an index in SQLite using the CREATE INDEX statement.

Syntax

The syntax to create an index in SQLite is:

CREATE [UNIQUE] INDEX [IF NOT EXISTS] index_name
  ON table_name 
    (column1 [ASC | DESC],
     column2 [ASC | DESC],
     ...
     column_n  [ASC | DESC])
  [ WHERE conditions ];

UNIQUE

It indicates that the combination of values in the indexed columns must be unique.

IF NOT EXISTS

Optional. If specified, the CREATE INDEX statement will not raise an error if the index already exists.

index_name

The name to assign to the index.

table_name

The name of the table in which to create the index.

column1, column2, ... column_n

The columns to use in the index.

ASC

Optional. The index is sorted in ascending order for that column.

DESC

Optional. The index is sorted in descending order for that column.

WHERE conditions

A partial index is created on only a subset of the records within the table.

Example

Let's look at an example of how to create an index in SQLite.

For example:

CREATE INDEX customer_idx
  ON customers (last_name);

In this example, we've created an index on the customers table called customer_idx. It consists of only one field - the last_name field.

We could also create an index with more than one field as in the example below:

CREATE INDEX customer_idx
  ON customers (state, city);

This would create an index called customer_idx that uses two columns - state and city.

Unique Index

To create a unique index on a table, you need to specify the UNIQUE keyword in the CREATE INDEX statement.

For example:

CREATE UNIQUE INDEX customer_unique_idx
  ON customers (file_number);

This example would create a unique index on the file_number field so that this field must always contains a unique value with no duplicates. This is a great way to enforce integrity within your database if you require unique values in columns that are not part of your primary key.

Partial Index

You could create a partial index on a table where only a subset of the records is included in the index.

For example:

CREATE INDEX customer_idx
  ON customers (last_name)
  WHERE last_name IS NOT NULL;

Because we have included a WHERE clause, a partial index is created on only those created where the last_name is not NULL. Records where the last_name is a NULL value will not be included in the index.

Drop an Index

You can drop an index in SQLite using the DROP INDEX statement.

Syntax

The syntax to drop an index in SQLite is:

DROP INDEX [IF EXISTS] index_name;

index_name

The name of the index to drop.

IF EXISTS

Optional. If specified, the DROP INDEX statement will not raise an error if the index does not exist.

Example

Let's look at an example of how to drop an index in SQLite.

For example:

DROP INDEX customer_idx;

In this example, we've dropped an index called customer_idx. Because each index name must be unique within the database, we do not have to specify the table name in the DROP INDEX statement.

Rename an Index

You can rename an index in SQLite by first dropping the index and then recreating the index with the new name.

Syntax

The syntax to rename an index in SQLite is:

DROP INDEX [IF EXISTS] index_name;

CREATE [UNIQUE] INDEX [IF NOT EXISTS] new_index_name
  ON table_name 
    (column1 [ASC | DESC],
     column2 [ASC | DESC],
     ...
     column_n  [ASC | DESC])
  [ WHERE conditions ];

index_name

The name of the index that you wish to rename.

new_index_name

The new name for the index.

Example

Let's look at an example of how to rename an index in SQLite.

For example:

DROP INDEX customer_idx;

CREATE INDEX customer_new_index
  ON customers (last_name);

In this example, we've renamed the index called customer_idx to customer_new_index.