What is Index?
An index is a data structure that improves the speed of searching for tables in a database.
When an index is created on a specific column of a table, the data in that column is sorted and stored along with the physical address of the data in a separate memory space. The column value and physical address are stored as a pair of (key, value).
You can think of an index as a table of contents or index in a book. When you find what you want in a book, you can find it much faster if you use the table of contents or the index. Similarly, you can find it faster if you use the index to find the data you want in a table.
Therefore, you can think of data = contents of the book, index = table of contents of the book, and physical address = page number of the book.
As mentioned above, indexes improve the speed and performance of table searches. In addition, the overall load of the system can be reduced accordingly.The key is that the data has a sorted form by index. In the past, in order to find data under a specific condition with a Where statement, a 'Full Table Scan' task was required to compare the entire table with the condition. can be quickly found.
In addition, ORDER BY statement or MIN/MAX can be executed quickly because they are already sorted.
Although there is an advantage that the index is always maintained in a sorted state, there are several disadvantages associated with it.-
Requires additional work to manage indexes
-
Requires additional storage space
-
If you use it incorrectly, it actually degrades search performance
Since the index must always be kept in a sorted state, the following additional operations are required when performing INSERT, DELETE, or UPDATE operations on the column to which the index is applied.
-
INSERT : add index for new data
-
DELETE: Performs an operation that does not use the index of the data to be deleted
-
UPDATE: Do not use existing index, add index for updated data
When to Use Indexes
In order to use an index efficiently, it is better to use it for a column with a wide range of data, less duplication, more queries, or a useful sorted state. Therefore, it is efficient to use an index in the following cases.
large table
Columns with infrequent INSERT, UPDATE, and DELETE operations
Columns where WHERE, ORDER BY, or JOIN are frequently used
Columns with low redundancy of data
