Junior
What is an index in a relational database?
sobes.tech AI
Answer from AI
An index is a data structure that improves the speed of search operations in a database. It creates a sorted list of values from one or more columns of a table, associated with the location of the corresponding rows. This allows the DBMS to quickly find the needed data without scanning the entire table.
Types of indexes:
- Primary Key: Uniquely identifies each row of the table. It is automatically created by the DBMS and guarantees the uniqueness of values.
- Unique: Ensures the uniqueness of values in one or more columns. Unlike the primary key, it can contain NULLs (in most DBMSs), and a table can have multiple unique indexes.
- Non-Unique: Accelerates search but does not require the values to be unique.
- Full-Text: Used to speed up text searches in string columns.
Advantages:
- Significantly speeds up the execution of SELECT queries, especially when searching, sorting, and joining tables.
Disadvantages:
- Takes additional disk space.
- Slows down INSERT, UPDATE, DELETE operations, as the index must also be updated when data changes.
Working principle (simplified): The DBMS uses the index as a pointer. Instead of sequentially scanning all table rows, it searches for the needed value in the index (which typically has a more efficient structure for searching, such as a B-tree) and instantly jumps to the required row in the table using the found pointer.
Example of creating an index in SQL:
CREATE INDEX index_lastname ON employees (lastname); // Creating an index on the lastname column in the employees table