MySQL Indexes

An index is a data structure maintained by MySQL to help locate rows more efficiently.

For example, if you frequently search students by email:

SELECT *
FROM students
WHERE email = 'amit@example.com';

An index on the email column can help MySQL locate the matching row without examining every row in the table.

Indexes are mainly used to improve the efficiency of data retrieval.

• Speed up searches.
• Improve filtering with WHERE conditions.
• Help JOIN operations.
• Help some ORDER BY operations.
• Help enforce uniqueness with unique indexes.
• Improve performance on suitable large-table queries.

CREATE INDEX index_name
ON table_name (column_name);

Example:

CREATE INDEX idx_student_name
ON students (name);

This creates an index named idx_student_name on the name column.

Suppose we frequently search students by city.

CREATE INDEX idx_student_city
ON students (city);

Now queries such as:

SELECT *
FROM students
WHERE city = 'Patna';

can potentially benefit from the index.

A single-column index contains one table column.

CREATE INDEX idx_email
ON students (email);

This is useful when email is frequently used for searching or filtering.

A composite index contains multiple columns.

CREATE INDEX idx_student_city_status
ON students (city, status);

This index can be useful for queries involving city and status, depending on the query and data distribution.

A composite index is an index containing two or more columns.

CREATE INDEX idx_course_status
ON students (course_id, status);

The order of columns in a composite index is important.

For a composite index such as:

CREATE INDEX idx_city_status
ON students (city, status);

The leading column is city.

The index can generally be useful for conditions involving:

city
city, status

But a query filtering only on status does not generally get the same benefit from this index because status is not the leading column.

A UNIQUE index prevents duplicate non-NULL values in the indexed key according to MySQL's unique-index rules.

CREATE UNIQUE INDEX idx_unique_email
ON students (email);

This is useful when each student should have a unique email address.

A PRIMARY KEY is indexed automatically by MySQL.

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    name VARCHAR(100)
);

The primary key provides an index structure for the primary key values, so you normally do not create another ordinary index on the same column for the same purpose.

A UNIQUE constraint is implemented using a unique index in MySQL.

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    email VARCHAR(150) UNIQUE
);

This creates a uniqueness rule for email and an associated unique index.

Indexes are important for columns used in relationships and JOINs.

CREATE INDEX idx_student_course
ON students (course_id);

This can help queries that frequently join students with courses using course_id.

You can see the indexes defined on a table using:

SHOW INDEX FROM students;

This displays information such as:

• Index name
• Indexed column
• Index uniqueness
• Column order
• Cardinality information

Another equivalent form is:

SHOW KEYS FROM students;

SHOW KEYS and SHOW INDEX provide index information for the table.

You can remove an index when it is no longer needed.

DROP INDEX idx_student_city
ON students;

The table and its data remain; only the specified index is removed.

An index can also be removed using ALTER TABLE.

ALTER TABLE students
DROP INDEX idx_student_city;

This performs the same basic index-removal operation.

Indexes are often considered for columns used frequently in WHERE conditions.

CREATE INDEX idx_student_status
ON students (status);

For example:

SELECT *
FROM students
WHERE status = 'Active';

Whether MySQL actually uses the index depends on the query, table size, statistics, data distribution, and optimizer decisions.

An appropriate index can sometimes help MySQL satisfy sorting requirements more efficiently.

CREATE INDEX idx_student_name
ON students (name);

Query:

SELECT *
FROM students
ORDER BY name;

However, MySQL may choose another execution strategy if it estimates that it is more efficient.

Indexes can help JOIN operations when the indexed columns are used effectively by the query.

CREATE INDEX idx_student_course
ON students (course_id);

Query:

SELECT
    s.name,
    c.course_name
FROM students s
INNER JOIN courses c
ON s.course_id = c.id;

An appropriate index can reduce the amount of data MySQL needs to examine.

The EXPLAIN statement helps you inspect how MySQL plans to execute a SELECT query.

EXPLAIN
SELECT *
FROM students
WHERE email = 'amit@example.com';

EXPLAIN can show information about possible and chosen indexes, access methods, estimated rows, and other execution details.

You can use EXPLAIN to investigate JOIN performance.

EXPLAIN
SELECT
    s.name,
    c.course_name
FROM students s
INNER JOIN courses c
ON s.course_id = c.id
WHERE s.city = 'Patna';

This helps you understand how MySQL plans to access the students and courses tables.

Index selectivity describes how effectively an index can distinguish rows.

For example, an index on a column containing many different email addresses can be highly selective.

An index on a column containing only two values such as:

Active
Inactive

may be less selective.

Indexes are not free. They require additional storage and must be maintained when indexed data changes.

For example:

INSERT INTO students (...);

UPDATE students
SET city = 'Patna'
WHERE student_id = 10;

DELETE FROM students
WHERE student_id = 10;

When indexed values are affected, MySQL may need to update the relevant index structures.

Creating an index for every column is usually not a good strategy.

Too many indexes can:

• Use more disk space.
• Increase write overhead.
• Make INSERT operations more expensive.
• Make UPDATE operations more expensive.
• Make DELETE operations more expensive.
• Increase database maintenance complexity.

For some string columns, MySQL can index only a specified number of leading characters.

CREATE INDEX idx_email_prefix
ON students (email(20));

This is called a prefix index.

Prefix indexes can reduce index size, but they may provide less precise filtering than indexing the full value.

The order of columns in a composite index matters.

CREATE INDEX idx_city_status_name
ON students (city, status, name);

The index starts with city, followed by status, then name.

A query filtering by city can potentially use the leading part of the index:

WHERE city = 'Patna'

While a query filtering only by name generally cannot use this composite index in the same way because name is not a leading column.

A covering index is an index that contains all the columns needed for a particular query, allowing MySQL to obtain the required information from the index without reading the base table for that query plan.

CREATE INDEX idx_student_city_name
ON students (city, name);

For example:

SELECT
    city,
    name
FROM students
WHERE city = 'Patna';

This query may be able to use the index as a covering index, depending on the execution plan.

• Creating indexes on every column without a reason.
• Creating duplicate or redundant indexes.
• Ignoring the order of columns in composite indexes.
• Not checking actual query performance.
• Ignoring INSERT, UPDATE, and DELETE overhead.
• Assuming an index will always be used.
• Forgetting to examine EXPLAIN output.

Suppose a student management system frequently searches by email, course, and city.

CREATE INDEX idx_students_email
ON students (email);

CREATE INDEX idx_students_course
ON students (course_id);

CREATE INDEX idx_students_city_status
ON students (city, status);

You can inspect them using:

SHOW INDEX FROM students;

And analyze queries using:

EXPLAIN
SELECT *
FROM students
WHERE city = 'Patna'
AND status = 'Active';

CREATE TABLE students (
    student_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(150),
    city VARCHAR(100),
    course_id INT,
    status VARCHAR(20)
);

INSERT INTO students
(name, email, city, course_id, status)
VALUES
('Amit Kumar', 'amit@example.com', 'Patna', 1, 'Active'),
('Priya Singh', 'priya@example.com', 'Gaya', 2, 'Active'),
('Rahul Sharma', 'rahul@example.com', 'Patna', 1, 'Active'),
('Neha Kumari', 'neha@example.com', 'Delhi', 3, 'Inactive');

-- Single-column index
CREATE INDEX idx_students_city
ON students (city);

-- Single-column index
CREATE INDEX idx_students_course
ON students (course_id);

-- Composite index
CREATE INDEX idx_students_city_status
ON students (city, status);

-- Unique index
CREATE UNIQUE INDEX idx_students_email
ON students (email);

-- Show indexes
SHOW INDEX FROM students;

-- Search using indexed columns
SELECT
    student_id,
    name,
    email
FROM students
WHERE email = 'amit@example.com';

-- Query using composite index columns
SELECT
    student_id,
    name,
    city,
    status
FROM students
WHERE city = 'Patna'
AND status = 'Active';

-- Analyze query execution
EXPLAIN
SELECT
    student_id,
    name
FROM students
WHERE city = 'Patna'
AND status = 'Active';

-- Analyze a JOIN
EXPLAIN
SELECT
    s.name,
    c.course_name
FROM students s
INNER JOIN courses c
ON s.course_id = c.id
WHERE s.city = 'Patna';

-- Remove an index
DROP INDEX idx_students_city
ON students;

This example demonstrates single-column indexes, composite indexes, unique indexes, SHOW INDEX, EXPLAIN, indexed searches, JOIN analysis, and dropping an index.