SQLite index

Index (Index) is a special lookup table used by database search engines to speed up data retrieval. Simply put, an index is a pointer to the data in the table. An index in a database is very similar to an index behind a book.

For example, if you wanted to cite all pages in a book that discusses a certain topic, you would first point to the index, which lists all topics in alphabetical order, and then point to one or more specific page numbers .

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, which allows you to name the index, specify the table and one or more columns to be indexed, and indicate whether the index is in ascending or descending order.

Indices can also be unique, similar to UNIQUE constraints, preventing duplicate entries on a column or combination of columns.

CREATE INDEX command

The basic syntax of CREATE INDEX is as follows:

CREATE INDEX index_name ON table_name;

Single column index

A single column index is an index created based on only one column of the 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, 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);

Combined index

Combined index is based on a table An index created on two or more columns. The basic syntax is as follows:

CREATE INDEX index_name

on table_name (column1, column2);

Whether you want to create a single column index or a combined index, you must consider Columns that are used very frequently in the WHERE clause as query filters.

If the value uses a column, choose to use a single column index. Choose to use a composite index if two or more columns are frequently used in the WHERE clause as a filter.

Implicit index

Implicit index is an index automatically created by the database server when creating an object. Indexes are automatically created with primary key constraints and unique constraints.

Example

The following 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

.indices command to list all available indexes on the COMPANY table as follows:

sqlite> . indices COMPANY

This will produce the following result, where sqlite_autoindex_COMPANY_1 is the implicit index created when the table is created.

salary_index
sqlite_autoindex_COMPANY_1

You can list all indexes in the database scope as follows:

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

DROP INDEX command

An index can be deleted using SQLite's DROP command. Special care should be taken when dropping indexes, as performance may either decrease or increase.

The basic syntax is as follows:

DROP INDEX index_name;

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

sqlite> DROP INDEX salary_index;

Under what circumstances should you avoid using indexes?

Although the purpose of indexes is to improve database performance, there are several situations where you need to avoid using indexes. When using indexes, you should reconsider the following guidelines:

Indexes should not be used on smaller tables.
Indices should not be used on tables with frequent large batch 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 frequently operated columns.

← SQLite trigger

SQLite Indexed By →