MySQL Indexes: Concept, Purpose, and Practical Examples

1. What is an Index in MySQL?

An index is a data structure that improves the speed of data retrieval operations on a database table at the cost of additional storage space and maintenance overhead. Think of it like an index in a book: instead of scanning every page to find a topic, you refer to the index to jump directly to the right page.

Key Points:

• Indexes do not change the data in the table.
• They provide a fast lookup mechanism.
• While indexes speed up SELECT queries, they slightly slow down INSERT, UPDATE, and DELETE operations because the index must be updated whenever the data changes.

2. Purpose of Using Indexes

The main purposes of using indexes in MySQL are:

1. Faster Query Performance
   Indexes allow MySQL to find rows quickly without scanning the entire table.
2. Efficient Sorting
   Indexes help optimize ORDER BY and GROUP BY operations.
3. Enforcing Uniqueness
   Unique indexes ensure that values in a column (or combination of columns) remain unique.
4. Optimizing Joins
   Indexes on columns used in joins improve the performance of queries involving multiple tables.

3. Types of Indexes

MySQL provides several types of indexes:

1. Primary Key Index
   Automatically created for the primary key column. Ensures uniqueness.
2. Unique Index
   Ensures all values in the column are unique.
3. Regular Index (Non-Unique Index)
   Speeds up retrieval but allows duplicate values.
4. Fulltext Index
   Optimized for text searches (used in MATCH and AGAINST).
5. Composite Index
   Index on multiple columns to optimize queries that filter on those columns together.

4. Creating Indexes in MySQL

A. Creating a Simple Index

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    department_id INT
);

-- Create an index on the last_name column
CREATE INDEX idx_last_name ON employees(last_name);

Explanation:

1. We create a table employees with basic columns.
2. CREATE INDEX idx_last_name ON employees(last_name); creates an index called idx_last_name on the last_name column.
3. Now, queries filtering by last_name will execute faster.

Example Query Using Index:

SELECT * FROM employees WHERE last_name = 'Smith';

• Without an index, MySQL scans all rows (FULL TABLE SCAN).
• With an index, MySQL can locate matching rows quickly.

B. Creating a Unique Index

CREATE UNIQUE INDEX idx_email ON employees(email);

• Ensures no two employees can have the same email.
• Useful for enforcing data integrity.

C. Creating a Composite Index

CREATE INDEX idx_dept_name ON employees(department_id, last_name);

Explanation:

• Optimizes queries that filter by department_id and last_name together.
• Example:

SELECT * FROM employees
WHERE department_id = 5 AND last_name = 'Smith';

• MySQL can use idx_dept_name to efficiently fetch results.

5. How Indexes Work

Indexes are typically implemented using a B-Tree or Hash structure:

• B-Tree Index: Maintains sorted data for range queries, equality checks, and sorting.
• Hash Index: Provides fast exact-match lookups (used in memory tables).

When a query uses a column with an index, MySQL searches the index first, finds the row’s location, and retrieves the data from the table. This avoids scanning every row, which is especially beneficial for large datasets.

6. When to Use Indexes

While indexes improve query performance, they come with trade-offs:

Use Indexes When:

• Columns are frequently used in WHERE, JOIN, ORDER BY, or GROUP BY clauses.
• Columns enforce uniqueness.

Avoid Indexing When:

• Columns have low cardinality (few unique values, e.g., a gender column).
• Tables are small (full table scans may be faster).
• Frequent updates on columns cause heavy index maintenance.

7. Checking Index Usage

You can check how MySQL uses indexes with the EXPLAIN statement:

EXPLAIN SELECT * FROM employees WHERE last_name = 'Smith';

• Shows whether the query uses idx_last_name.
• Helps optimize query performance.