What is an index in a relational database?

An index in a relational database is a data structure that improves the speed of data retrieval operations from a table. It works on the principle of a pointer, speeding up record searches by a specific column (or set of columns) without the need to scan the entire table.

Types of indexes:

• Clustered: Defines the physical order of data storage in the table. A table can have only one clustered index.
• Non-clustered: Creates a separate structure that stores pairs of "column value - row pointer." A table can have multiple non-clustered indexes.

Advantages of using indexes:

• Speed up SELECT queries: Significantly reduce the time to execute read data queries.
• Speed up JOIN operations: Optimize table joins.
• Ensure uniqueness: Unique indexes guarantee the uniqueness of values in the indexed column.

Disadvantages of using indexes:

• Slow down INSERT, UPDATE, DELETE operations: When data in the table changes, the database also needs to update the index.
• Additional disk space: Indexes occupy space on the disk.
• Need for maintenance: Indexes require maintenance and rebuilding during significant data changes.

Example of creating an index in SQL:

-- Creating a simple index on one column
CREATE INDEX idx_lastname ON employees (lastname);

-- Creating a composite index on multiple columns
CREATE INDEX idx_name_city ON customers (firstname, city);

-- Creating a unique index
CREATE UNIQUE INDEX uq_email ON users (email);