Indexes

Introduction

Indexes in SQL are used to speed up the retrieval of data from a database table by creating pointers to the data in the table. This section will cover the types of indexes, their syntax, and examples of how to use them.

What is an Index?

An index is a database object that improves the speed of data retrieval operations on a table at the cost of additional storage space and potential overhead on data modification operations (INSERT, UPDATE, DELETE).

Types of Indexes

Single-Column Index

An index created on a single column of a table.

CREATE INDEX index_name ON table_name (column_name);

Example

Create an index on the last_name column in the employees table:

CREATE INDEX idx_last_name ON employees (last_name);

Composite Index

An index created on multiple columns of a table.

CREATE INDEX index_name ON table_name (column1, column2, ...);

Example

Create a composite index on the last_name and first_name columns in the employees table:

CREATE INDEX idx_name ON employees (last_name, first_name);

Unique Index

An index that ensures all values in the indexed column(s) are unique.

CREATE UNIQUE INDEX index_name ON table_name (column_name);

Example

Create a unique index on the email column in the customers table:

CREATE UNIQUE INDEX idx_unique_email ON customers (email);

Managing Indexes

Dropping an Index

You can drop an index using the DROP INDEX statement.

DROP INDEX index_name ON table_name;

Example

Drop the idx_last_name index from the employees table:

DROP INDEX idx_last_name ON employees;

Viewing Indexes

You can view the indexes on a table using the SHOW INDEX statement (MySQL) or querying the INFORMATION_SCHEMA. Other databse engines may have different methods for viewing indexes also, please see the DuckDB example.

Example (MySQL)

Show indexes on the employees table:

SHOW INDEX FROM employees;

Example (DuckDB)

Select
  *
from duckdb_constraints()

When to Use Indexes

• Use indexes on columns frequently used in WHERE clauses.
• Use indexes on columns frequently used in JOIN operations.
• Use indexes on columns frequently used in ORDER BY clauses.
• Use unique indexes on columns that must contain unique values.

Considerations and Trade-offs

• Indexes improve read performance but can slow down write operations.
• Indexes require additional storage space.
• Too many indexes can lead to maintenance overhead and decreased performance.

Practice Exercises

• Create an index on the employee table for the employee names and salary
• create an index on the customer table for email

---