SQLite: Indexes
This SQLite tutorial 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.
Advertisements