SQLite Indexes

Indexes are special lookup tables that database search engines can use to accelerate data retrieval. In short, anindexAn index is a pointer to the data in the table. The indexes in a database are very similar to the indexes at the back of a book.

For example, if you want to refer to all the pages in a book that discuss a particular topic, you first need to refer to the index, which lists all topics in alphabetical order and then references one or more specific page numbers.

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

Creating an index involves the CREATE INDEX statement, which allows you to name the index, specify the table, and the columns or column combinations to be indexed, and indicate whether the index should be in ascending or descending order.

Indexes can also be unique, similar to UNIQUE constraints, because indexes can prevent duplicate entries in the columns or column combinations they index.

CREATE INDEX command

The basic syntax is as followsCREATE INDEX.

CREATE INDEX index_name ON table_name;

Single-column index

A single-column index is an index created based on a single table column. The basic syntax is as follows-

CREATE INDEX index_name ON table_name (column_name);

Unique index

Unique index is not only used to improve performance but also to ensure 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 on two or more columns of a table. The basic syntax is as follows-

CREATE INDEX index_name ON table_name (column1, column2);

Whether creating a single-column index or a composite index, consider the column(s) that you may use very frequently as a filter condition in the WHERE clause of your queries.

If only one column is used, a single-column index should be selected. If two or more columns are frequently used as filters in the WHERE clause, a composite index will be the best choice.

Implicit index

Implicit indexes are indexes automatically created by the database server when an object is created. Automatic indexes are created for primary key constraints and unique constraints.

Example

Here is an example, where we will create an index for the salary column in the COMPANY table-

sqlite> CREATE INDEX salary_index ON COMPANY (salary);

Now, let's use the following.indicesThe command lists all available indexes in the COMPANY table:

sqlite> .indices COMPANY

This will produce the following results, wheresqlite_autoindex_COMPANY_1It is an implicit index,The implicitThe explicit index is created when the table itself is created.

salary_indexsqlite_autoindex_COMPANY_1

You can list all index database ranges as follows:

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

DROP INDEX command

SQLite can be used DROPCommand to delete index. Be careful when deleting an index, as performance may slow down or improve.

The following is the basic syntax as follows-

DROP INDEX index_name;

You can use the following statement to delete previously created indexes.

sqlite> DROP INDEX salary_index;

When should indexes be avoided?

Although indexes are intended to improve database performance, they should sometimes be avoided. The following guidelines indicate when it is advisable to reconsider the use of indexes.

Indexes should not be used for-

• Small tables.

• Tables with frequent, large batch update or insert operations.

• Columns containing a large number of NULL values.

• Frequently operated columns.