May 16, 2021 SQLite
Index is a special lookup table that database search engines use to speed up data retrieval. S imply put, an index is a pointer to the data in the table. The index in a database is very similar to the index at the back of a book.
For example, if you want to reference all pages in a book that discusses a topic, you first need to point to the index, which lists all topics in alphabetical order, and then to one or more specific page numbers.
Indexing helps speed up SELECT queries and WHERE clauses, but it slows down data entry when using UPDATE and INSERT statements. Indexes can be created or deleted without affecting the data.
Create an index using the CREATE INDEX statement, which allows you to name the index, specify the table and one or more columns to index, and indicate whether the index is in ascending or descending order.
Indexes can also be unique, similar to UNIQUE constraints, to prevent duplicate entries on columns or column combinations.
The basic syntax of CREATE INDEX is as follows:
CREATE INDEX index_name ON table_name;
A single-column index is an index that is created on only one column based on a table. The basic syntax is as follows:
CREATE INDEX index_name ON table_name (column_name);
Unique indexes are used not only for performance, but also for the integrity of the data. T he unique index does not allow any duplicate values to be inserted into the table. The basic syntax is as follows:
CREATE INDEX index_name on table_name (column_name);
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, consider that you use columns very frequently in the WHERE clause that is the filter for queries.
If the value is used to a column, you choose to use a single-column index. If two or more columns are frequently used in the WHERE clause as filters, choose to use a combined index.
An implicit index is an index that is automatically created by the database server when an object is created. Indexes are automatically created as primary and unique constraints.
Here's an example, and we'll create an index on the salary column of the COMPANY table:
sqlite> CREATE INDEX salary_index ON COMPANY (salary);
Now let's use the .indices command to list all the available indexes on the COMPANY table, as follows:
sqlite> .indices COMPANY
This produces the following results, where sqlite_autoindex_COMPANY_1 is an implicit index that was created when the table was created.
salary_index sqlite_autoindex_COMPANY_1
You can list all indexes for the database range, as follows:
sqlite> SELECT * FROM sqlite_master WHERE type = 'index';
An index can be deleted using SQLite's DROP command. Special care should be taken when deleting indexes, as performance may decrease or improve.
The basic syntax is as follows:
DROP INDEX index_name;
You can use the following statement to delete the index you created earlier:
sqlite> DROP INDEX salary_index;
Although indexing is intended to improve the performance of your database, 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.
Indexes should not be used on tables with frequent large numbers of updates or insertions.
The index should not be used on columns that contain a large number of NULL values.
The index should not be used on columns that operate frequently.