Easy Tutorial
Sqlite Alias Sqlite Like Clause Sqlite Syntax Sqlite Php Sqlite Distinct Keyword Sqlite Functions Sqlite Autoincrement Sqlite Detach Database Sqlite Unions Clause Sqlite Attach Database Sqlite Joins Sqlite Pragma Sqlite Group By Sqlite Where Clause Sqlite Injection Sqlite And Or Clauses Sqlite Operators Sqlite Constraints Sqlite Drop Table Sqlite C Cpp Sqlite Order By Sqlite Subquery Sqlite Expressions Sqlite View Sqlite Indexed By Sqlite Alter Command Sqlite Insert Sqlite Select Sqlite Explain Sqlite Python Sqlite Installation Sqlite Transaction Sqlite Update Sqlite Delete Sqlite Commands Sqlite Date Time Sqlite Null Values Sqlite Create Table Sqlite Java Sqlite Data Types Sqlite Trigger Sqlite Create Database Sqlite Limit Clause Sqlite Intro Sqlite Truncate Table Sqlite Vacuum Sqlite Perl Sqlite Glob Clause Sqlite Index Sqlite Tutorial Sqlite Having Clause
❮ Sqlite Glob Clause Sqlite Tutorial ❯

SQLite Index

An index is a special lookup table that database search engines use 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 in the back of a book.

Taking the index page of a Chinese dictionary as an example, we can quickly find the required character using the sorted index by pinyin, stroke, or radical.

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 dropped without affecting the data.

An index is created using the CREATE INDEX statement, which allows naming the index, specifying the table and columns to be indexed, and indicating whether the index is in ascending or descending order.

Indexes can also be unique, similar to UNIQUE constraints, preventing duplicate entries on columns or column combinations.

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 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 based 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 frequently used as query filters in the WHERE clause.

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

Implicit Index

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

Example

Below is an example where we will create an index on the salary column of the COMPANY table:

sqlite> CREATE INDEX salary_index ON COMPANY (salary);

Now, let's list all available indexes on the COMPANY table using the .indices or .indexes command, as shown below:

sqlite> .indices COMPANY

This will produce the following result, where sqlite_autoindex_COMPANY_1 is the implicit index created when the table was 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 an index, as performance may decrease or increase.

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 to Avoid Using Indexes?

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

❮ Sqlite Glob Clause Sqlite Tutorial ❯