Index

What Is an Index?

An index is a special data structure that speeds up searching and retrieving data from a table. Without an index, the database must scan the entire table (full table scan).

Why Are Indexes Important?

Without an index, SELECT queries on large tables will be very slow. An index works like the index in a book.

Creating an Index (CREATE INDEX)

CREATE INDEX idx_name ON EMPLOYEE(name);

CREATE INDEX idx_dept_status ON EMPLOYEE(department, status);

Unique Index

A unique index ensures there are no duplicates in the indexed column.

CREATE UNIQUE INDEX idx_email ON EMPLOYEE(email);

Dropping an Index (DROP INDEX)

DROP INDEX idx_name ON EMPLOYEE;

Viewing Existing Indexes

SHOW INDEX FROM EMPLOYEE;

When to Use an Index?

Optimization Example

-- Without an index: slow on large tables
SELECT * FROM EMPLOYEE WHERE name = 'James';

-- With an index: fast search
CREATE INDEX idx_name ON EMPLOYEE(name);
SELECT * FROM EMPLOYEE WHERE name = 'James';

Trade-off: Speed vs Storage

• Indexes speed up SELECT but slow down INSERT, UPDATE, and DELETE
• Every index takes up additional storage space
• Use indexes in moderation, not excessively

Tips

• Index columns that are frequently used in WHERE, JOIN, or ORDER BY
• Use composite indexes for multi-column queries
• Monitor and remove indexes that are not used

Understanding indexes helps with query optimization. Next, we will learn about Transactions in the next chapter.