1.35. SQLite Index (Index)

Index is a special look-up table, which is used by database search engine to speed up data retrieval. Simply put, an index is a pointer to the data in a table. The index in a database is very similar to the index catalogue of a book.

Taking the catalogue page (index) of the Chinese dictionary as an example, we can quickly find the words we need according to the catalogue (index) sorted by pinyin, strokes, partial radicals, etc.

Indexing helps speed up SELECT Query and WHERE Clause, but it slows down the use of UPDATE And INSERT The data input in the statement. Indexes can be created or deleted, but the data is not affected.

Create an index using the CREATE INDEX statement, which 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.

The index can also be unique, as opposed to UNIQUE Similarly, constraints prevent duplicate entries on a column or on a combination of columns.

1.35.1. CREATE INDEX command ¶

CREATE INDEX The basic syntax is as follows:

CREATE INDEX index_name ON table_name; 

1.35.2. 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); 

1.35.3. Unique index ¶

Unique indexes are used not only for performance, but also for data integrity. Unique indexes do 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); 

1.35.4. Combinatorial 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 you want to create a single-column index or a combined index, take into account the WHERE A column that is used very frequently in a clause.

If only one column is used, choose to use a single column index. If, as a filter, WHERE If two or more columns are frequently used in the clause, choose to use a combined index.

1.35.5. Implicit index ¶

An implicit index is an index created automatically by the database server when an object is created. Indexes are automatically created as primary key constraints and unique constraints.

1.35.6. Example ¶

Here is an example of what we will do in COMPANY Tabular salary Create an index on the column:

sqlite> CREATE INDEX salary_index ON COMPANY (salary); 

Now, let’s use the .indices Or .indexes Command list COMPANY All available indexes on the table, as follows:

sqlite> .indices COMPANY 

This will produce the following results, where sqlite_autoindex_COMPANY_1 Is an implicit index created when a table is created.

salary_index sqlite_autoindex_COMPANY_1

You can list all database-wide indexes, as follows:

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

1.35.7. DROP INDEX command ¶

An index can use SQLite’s DROP Command to delete. Special care should be taken when deleting indexes, as performance may degrade 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; 

1.35.8. Under what circumstances should indexes be avoided? ¶

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

Indexes should not be used on smaller tables.
Indexes should not be used on tables that have frequent bulk updates or inserts.
Indexes should not be used on columns that contain a large number of NULL values.
Indexes should not be used on columns that operate frequently.