SQLite | Index | What Indexes Are and Their Pros and Cons
Searching a table for specific data can take a long time when the table contains many columns or a large amount of data. Creating indexes on appropriate columns can speed up these searches. This article briefly explains indexes and the advantages and disadvantages of using them.
What Is an Index?
An index extracts values from columns that are frequently used in searches and organizes them so that they are easy to find. Consider a table with the following four columns.
| id | name | address | old |
|---|---|---|---|
| 1 | devkuma | Seoul | 23 |
| 2 | kimkc | Busan | 19 |
| 3 | araikuma | Seoul | 38 |
| 4 | mykuma | Daejeon | 18 |
| 5 | yourkuma | Seoul | 17 |
| 6 | happykuma | Seoul | 24 |
When searching the name column, the database may need to examine the stored rows in sequence because the data is not ordered by name. This is not a problem for such a small data set, but scanning from the beginning is highly inefficient when millions of rows are stored.
Creating an index can improve search performance in this situation. In simple terms, an index processes and stores data from the target column in a form that can be searched quickly. For example, an index on the name column might look as follows.
| id | name |
|---|---|
| 3 | araikuma |
| 1 | devkuma |
| 2 | kimkc |
| 6 | happykuma |
| 4 | mykuma |
| 5 | yourkuma |
This index retains only the values from the id and name columns and stores the rows sorted by name in ascending order. Searching it is faster than searching the original table because it contains less data and is already sorted.
Indexes can use various techniques, such as B-trees or function-based approaches, to accelerate searches. The important point is that an index stores only the required data separately from the table in a form optimized for searching.
Advantages and Disadvantages of Indexes
Indexes are useful, but they do not provide only advantages. Because an index maintains its own data separately from the table, adding data to the table also updates the index. Its ordering must also be maintained, which makes inserts slower.
Creating an index on a small table or on a column with few distinct values may provide little benefit. An index on a column that is rarely searched is also unlikely to be useful.
Indexes improve searches but add overhead when data is inserted. Create one only after determining that it is needed.