MySQL Indexes

An index in MySQL is a database object that speeds up the retrieval of rows from a table. It works similarly to an index at the back of a book — instead of reading every page to find a topic, the index points directly to the relevant page. Similarly, a database index allows MySQL to find data without scanning every row in a table.

Why Use Indexes?

When a table has thousands or millions of rows, a query without an index performs a full table scan — it reads every single row to find matching data. This is slow. An index narrows the search significantly and returns results much faster.

Real-World Analogy

A phone directory lists names alphabetically. Finding "Sharma, Ravi" takes seconds because of alphabetical ordering. Without that order, finding one name among millions would take forever. An index creates a similar structured shortcut in the database.

Types of Indexes in MySQL

Creating an Index

Basic Index on One Column

CREATE INDEX idx_name ON employees (emp_name);

UNIQUE Index

CREATE UNIQUE INDEX idx_email ON users (email);

Composite Index (Multiple Columns)

CREATE INDEX idx_name_dept ON employees (emp_name, department);

Index During Table Creation

CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    emp_name VARCHAR(100),
    department VARCHAR(50),
    INDEX idx_dept (department)
);

Example: Index in Action

CREATE TABLE products (
    product_id INT AUTO_INCREMENT PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    category VARCHAR(50),
    price DECIMAL(8,2)
);

CREATE INDEX idx_category ON products (category);

Now, searching by category is much faster:

SELECT * FROM products WHERE category = 'Electronics';

MySQL uses idx_category to locate matching rows quickly instead of scanning the whole table.

Viewing Indexes on a Table

SHOW INDEX FROM employees;

This displays all indexes on the table including their names, columns, and types.

Dropping an Index

DROP INDEX idx_name ON employees;

When to Use Indexes

• Columns used frequently in WHERE clauses
• Columns used in JOIN conditions
• Columns used in ORDER BY or GROUP BY
• Columns with a high number of unique values

When Not to Use Indexes

• Tables with very few rows
• Columns that are updated very frequently
• Columns with very low uniqueness (e.g., a gender column with only M/F values)

Trade-offs of Indexes

Key Points

• Indexes improve query performance by reducing the number of rows MySQL scans.
• MySQL automatically creates an index on the PRIMARY KEY and UNIQUE columns.
• Composite indexes cover searches involving multiple columns.
• Use SHOW INDEX FROM table_name to view all indexes on a table.
• Indexes speed up reads but slightly slow down writes — use them wisely.