SQLite Index

An index is a special lookup table that the database search engine uses to speed up data retrieval. Simply put, an index is a pointer to data in a table. An index in a database is very similar to the index of a book.

Take the index pages of a Chinese dictionary as an analogy. You can quickly find the character you need using tables sorted by pinyin, stroke count, radicals, and so on.

Indexes help speed up SELECT queries and WHERE clauses, but they slow down data input when using UPDATE and INSERT statements. Indexes can be created or deleted without affecting the data.

Use the CREATE INDEX statement to create an index. It allows you to name the index, specify the table and the column or columns to be indexed, and indicate whether the index is in ascending or descending order.

Indexes can also be unique, similar to the UNIQUE constraint, preventing duplicate entries on a column or combination of columns.

CREATE INDEX Command

CREATE INDEXThe basic syntax is as follows:

CREATE INDEX index_name ON table_name;

Single-Column Index

A single-column index is an index created on only one column of a table. The basic syntax is as follows:

CREATE INDEX index_name
ON table_name (column_name);

Unique Index

Using a unique index is not only for performance, but also for data integrity. A unique index does not allow any duplicate values to be inserted into the table. The basic syntax is as follows:

CREATE UNIQUE INDEX index_name
on table_name (column_name);

Composite Index

A composite index is an index created on two or more columns of a table. The basic syntax is as follows:

CREATE INDEX index_name
on table_name (column1, column2);

Whether to create a single-column index or a composite index depends on the columns you use very frequently in the WHERE clause as query filter conditions.

If only one column is used, choose a single-column index. If two or more columns are frequently used in the WHERE clause as filters, choose a composite index.

Implicit Index

Implicit indexes are automatically created by the database server when objects are created. Indexes are automatically created for primary key constraints and unique constraints.

Example

Below is an example. We will create an index on the salary column of the COMPANY table:

sqlite> CREATE INDEX salary_index ON COMPANY (salary);

Now, let us use the.indicesor.indexescommand to list all available indexes on the COMPANY table, as shown below:

sqlite> .indices COMPANY

This will produce the following result, wheresqlite_autoindex_COMPANY_1is the implicit index created when the table was created.

salary_index
sqlite_autoindex_COMPANY_1

You can list all indexes in the database as follows:

sqlite> SELECT * FROM sqlite_master WHERE type = 'index';

DROP INDEX Command

An index can be removed using SQLite'sDROPcommand. Special care should be taken when deleting an index, because performance may either decrease or improve.

The basic syntax is as follows:

DROP INDEX index_name;

You can use the following statement to delete the previously created index:

sqlite> DROP INDEX salary_index;

When should indexes be avoided?

Although the purpose of indexes is to improve database performance, there are several situations where indexes should be avoided. Consider the following guidelines when using indexes:

  • Indexes should not be used on small tables.

  • Indexes should not be used on tables that have frequent large-scale update or insert operations.

  • Indexes should not be used on columns that contain a large number of NULL values.

  • Indexes should not be used on columns that are frequently manipulated.

Other Extensions