SQL Indexes

An index is a data structure used by the database to find records more efficiently.

CREATE INDEX idx_student_name
ON students(name);

This creates an index on the name column of the students table.

Indexes can improve the performance of queries that search large tables.

• Faster searching.
• Faster filtering with WHERE.
• Can improve JOIN performance.
• Can help with ORDER BY operations.
• Can enforce uniqueness when using UNIQUE indexes.

The basic syntax is:

CREATE INDEX index_name
ON table_name(column_name);

For example:

CREATE INDEX idx_name
ON students(name);

You can create an index on one column.

CREATE INDEX idx_email
ON students(email);

This can help queries that frequently search students by email.

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

A composite index contains more than one column.

CREATE INDEX idx_student_course
ON students(course_id, status);

This can be useful for queries that commonly filter using these columns together.

A composite index is an index created using multiple columns.

CREATE INDEX idx_name_city
ON students(name, city);

The order of columns in a composite index matters because the database can use the index according to its column order and query conditions.

A UNIQUE index prevents duplicate values in the indexed key.

CREATE UNIQUE INDEX idx_email
ON students(email);

This ensures that duplicate email values are not allowed in the indexed column, subject to the database's NULL handling rules.

A PRIMARY KEY is automatically indexed by MySQL.

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

Therefore, you normally do not need to create another ordinary index on the primary key column.

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

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

The database creates the required unique index for the UNIQUE column.

Indexes can help queries that frequently filter records using WHERE.

CREATE INDEX idx_status
ON students(status);

SELECT *
FROM students
WHERE status = 'Active';

An appropriate index may allow the database to find matching rows more efficiently.

An index may also help queries that frequently sort or access data in a particular order.

CREATE INDEX idx_marks
ON students(marks);

SELECT name, marks
FROM students
ORDER BY marks DESC;

The optimizer decides whether the index is beneficial for the query.

Indexes can improve joins when columns used for matching are indexed appropriately.

CREATE INDEX idx_course_id
ON students(course_id);

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

Indexes on join columns can be useful, especially for large tables.

In MySQL, use SHOW INDEX to view indexes on a table.

SHOW INDEX FROM students;

This displays information such as index name, indexed columns, uniqueness, and sequence.

Use DROP INDEX to remove an index.

DROP INDEX idx_student_name
ON students;

Dropping an unnecessary index can reduce index maintenance overhead.

An index can also be created using ALTER TABLE.

ALTER TABLE students
ADD INDEX idx_city (city);

This creates an index named idx_city on the city column.

You can create a unique index using ALTER TABLE.

ALTER TABLE students
ADD UNIQUE INDEX idx_email (email);

This creates a unique index on the email column.

For some string columns, MySQL allows indexing only a prefix of the value.

CREATE INDEX idx_name_prefix
ON students(name(10));

This indexes the first 10 characters of the column value.

Prefix indexes can reduce index size, but their usefulness depends on the data and query pattern.

Indexes can contain NULL values in MySQL when the indexed column permits NULL.

CREATE INDEX idx_mobile
ON students(mobile);

If mobile allows NULL, rows containing NULL can still be part of the index.

An index can sometimes help with LIKE searches that use a fixed starting pattern.

CREATE INDEX idx_name
ON students(name);

SELECT *
FROM students
WHERE name LIKE 'Rah%';

A pattern beginning with a wildcard, such as %rah, generally cannot use a normal B-tree index efficiently for the leading part of the search.

Indexes can improve SELECT performance, but they also have a cost.

INSERT INTO students
(name, email, city)
VALUES
('Rahul', 'rahul@example.com', 'Patna');

When a row is inserted, relevant indexes also need to be maintained. Therefore, having too many indexes can slow write operations.

Updating an indexed column may require the corresponding index entry to be updated.

UPDATE students
SET email = 'new@example.com'
WHERE id = 5;

Indexes can help locate the row, but changing indexed values also creates maintenance work.

Indexes can help locate rows for DELETE operations, but index entries also have to be maintained.

DELETE FROM students
WHERE email = 'old@example.com';

The database removes the corresponding index entries when the row is deleted.

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

• Indexes consume storage.
• INSERT operations can become slower.
• UPDATE operations can require more index maintenance.
• DELETE operations also require index maintenance.
• Unused indexes add unnecessary overhead.

Create indexes based on actual query requirements.

Common candidates for indexes include columns frequently used in:

• WHERE conditions
• JOIN conditions
• ORDER BY operations
• GROUP BY operations in appropriate query patterns
• UNIQUE constraints

Actual performance should be checked using query plans and real workloads.

The order of columns in a composite index is important.

CREATE INDEX idx_course_status
ON students(course_id, status);

This index is organized first by course_id and then by status. Queries using the leading column are generally more likely to benefit from the index.

MySQL's EXPLAIN statement can help you understand how a query is executed.

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

The output can show whether an index is being considered or used and help identify inefficient query plans.

• Creating indexes on every column.
• Creating duplicate indexes.
• Ignoring the order of columns in composite indexes.
• Keeping indexes that are never used.
• Assuming every query automatically becomes faster with an index.
• Not checking query execution plans.

Suppose a student table contains thousands of records and users frequently search by email and course.

CREATE INDEX idx_student_email
ON students(email);

CREATE INDEX idx_student_course
ON students(course_id);

Now queries such as the following can potentially benefit from the indexes:

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

SELECT *
FROM students
WHERE course_id = 3;

Consider the following students table:

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

Create indexes for commonly searched columns:

CREATE INDEX idx_student_name
ON students(name);

CREATE INDEX idx_student_city
ON students(city);

CREATE INDEX idx_student_course_status
ON students(course_id, status);

CREATE UNIQUE INDEX idx_student_email
ON students(email);

Check the indexes:

SHOW INDEX FROM students;

Remove an unnecessary index:

DROP INDEX idx_student_city
ON students;

This demonstrates how indexes can be created, inspected, and removed according to the application's query requirements.