SQLite | Indexes | Creating an Index

Tags:

This article explains how to create an index in SQLite and list the indexes in a database.

Creating an Index with CREATE INDEX

Use the following syntax.

CREATE INDEX index_name ON table_name (column1, column2, ...);

An index targets columns in a table. It can cover one column or a combination of several columns.

Create a table for the example.

create table user (name text, old integer, address text);

sqlite> create table user (name text, old integer, address text);
sqlite>

Insert data into the table.

insert into user values ('devkuma', 28, 'Seoul');
insert into user values ('kimkc', 22, 'Busan');
insert into user values ('araikuma', 32, 'Seoul');
insert into user values ('happykuma', 23, 'Seoul');
insert into user values ('mykuma', 23, 'Daejeon');

sqlite> insert into user values ('devkuma', 28, 'Seoul');
sqlite> insert into user values ('kimkc', 22, 'Busan');
sqlite> insert into user values ('araikuma', 32, 'Seoul');
sqlite> insert into user values ('happykuma', 23, 'Seoul');
sqlite> insert into user values ('mykuma', 23, 'Daejeon');
sqlite>

Create an index named nameindex on the name column.

create index nameindex on user (name);

sqlite> create index nameindex on user (name);
sqlite>

The index is now available.

Searching Data with an Index

An index can make some searches faster, but queries are written the same way whether an index exists or not. SQLite automatically uses a suitable index when it executes a query.

For example, query the indexed name column as follows.

select * from user where name = 'devkuma';

sqlite> select * from user where name = 'devkuma';
devkuma|28|Seoul

The query does not need to explicitly refer to the index.

Listing Indexes in a Database

Use the SQLite .indices command.

.indices
.indices? TABLE?

Without an argument, it lists all indexes. With a table name, it lists indexes associated with matching tables.

List all indexes.

.indices

sqlite> .indices
nameindex
sqlite>

The output includes the index created on this page.

sqlite> .indices user
nameindex
sqlite>

The second command displays nameindex, which targets the user table.