MySQL Indexes

An index is a separate data structure that MySQL maintains alongside a table to make lookups much faster. Without an index, MySQL has to scan every single row in a table to find matching data — a process called a full table scan. With an index on the right column, MySQL can jump directly to the relevant rows, like using an index at the back of a book instead of reading every page.

Indexes are one of the most powerful tools for improving query performance — especially on large tables.

---

Types of Indexes in MySQL

---

Creating an Index in CREATE TABLE

You can define indexes directly inside a CREATE TABLE statement:

CREATE TABLE articles (
    id INT NOT NULL AUTO_INCREMENT,
    title VARCHAR(200) NOT NULL,
    author_id INT NOT NULL,
    published_at DATE NOT NULL,
    PRIMARY KEY (id),
    INDEX idx_author (author_id),
    INDEX idx_published (published_at)
);

Here, two indexes are created: one on author_id (to quickly find all articles by a specific author) and one on published_at (to sort or filter articles by date efficiently).

---

Creating an Index with CREATE INDEX

You can also add an index to an existing table using the standalone CREATE INDEX statement:

CREATE INDEX idx_author ON articles (author_id);

For a unique index:

CREATE UNIQUE INDEX idx_email ON users (email);

---

Adding an Index with ALTER TABLE

Alternatively, use ALTER TABLE to add an index:

ALTER TABLE articles
ADD INDEX idx_published (published_at);

---

Composite Index

A composite (multi-column) index covers two or more columns together. It is useful when queries frequently filter or sort by multiple columns at once:

CREATE INDEX idx_author_date ON articles (author_id, published_at);

---

Viewing Indexes on a Table

To see all indexes currently defined on a table, use:

SHOW INDEX FROM articles;

This returns each index's name, the columns it covers, and whether it is unique.

---

Dropping an Index

To remove an index that is no longer needed:

DROP INDEX idx_author ON articles;

Or with ALTER TABLE:

ALTER TABLE articles
DROP INDEX idx_author;

---

When to Use Indexes

Add an index on a column when:

• It is frequently used in a WHERE clause to filter rows.
• It is used in JOIN conditions to link tables.
• It is used in ORDER BY or GROUP BY to sort results.
• It is a foreign key column — MySQL does not create this index automatically.

Avoid over-indexing. While indexes speed up reads, they slow down writes (INSERT, UPDATE, DELETE) because MySQL must update the index every time data changes. They also use additional disk space.

---

Checking if MySQL Uses Your Index

Use EXPLAIN to see how MySQL executes a query and whether it uses an index:

EXPLAIN SELECT * FROM articles WHERE author_id = 5;

Look at the key column in the result — if it shows your index name, MySQL is using it. If it shows NULL, MySQL is doing a full table scan and you may want to consider adding an index.

---

Key Points to Remember

• An index speeds up data retrieval by allowing MySQL to find rows without scanning the entire table.
• MySQL automatically creates an index for PRIMARY KEY and UNIQUE columns.
• Regular indexes (INDEX) do not enforce uniqueness — they only improve read performance.
• Composite indexes cover multiple columns and are useful for multi-condition queries.
• Indexes speed up reads but slow down writes — add them only where they are genuinely needed.
• Use EXPLAIN to verify that your queries are actually using the indexes you created.