YouTip

# SQLite Index An index is a special lookup table that the database search engine can use to speed up data retrieval. Simply put, an index is a pointer to the data in a table. An index in a database is very similar to an index in a book. Using the table of contents (index) of a Chinese dictionary as an example, we can quickly find the characters we need through indexes sorted by pinyin, stroke count, radicals, etc. 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. Use the CREATE INDEX statement to create an index. It allows naming the index, specifying the table and one or more columns to be indexed, and indicating whether the index is in ascending or descending order. An index can also be unique, similar to a UNIQUE constraint, preventing duplicate entries on a column or a combination of columns. ## CREATE INDEX Command The basic syntax for **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 you frequently use in the WHERE clause as query filters. If only one column is used, choose a single-column index. If two or more columns are frequently used in the WHERE clause as filters, choose a composite index. ## 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 and UNIQUE constraints. ## Example Here 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 use the .indices or .indexes command to list all available indexes on the COMPANY table, 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 dropped using SQLite's **DROP** command. Be cautious when dropping an index, as performance may degrade or improve. The basic syntax is as follows: DROP INDEX index_name; You can drop the previously created index using the following statement: 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 you should avoid using them. When using indexes, reconsider the following guidelines: * Indexes should not be used on small tables. * Indexes should not be used on tables with frequent large batch updates or insertions. * Indexes should not be used on columns with a large number of NULL values. * Indexes should not be used on frequently updated columns.