MySQL Indexes

Introduction

In this chapter, we will learn about indexes in MySQL. Indexes are database objects that improve the speed of data retrieval operations on a table at the cost of additional storage space and slight overhead on data modification operations (INSERT, UPDATE, DELETE). Indexes are crucial for optimizing the performance of database queries, especially on large datasets. We will cover the syntax for creating indexes, examples, and important considerations for using indexes in MySQL.

Types of Indexes

1. Primary Key Index: Automatically created when a primary key is defined.
2. Unique Index: Ensures all values in a column or a set of columns are unique.
3. Full-Text Index: Used for full-text searches.
4. Spatial Index: Used for spatial data types.
5. Regular Index: Improves the performance of queries on non-unique columns.

Syntax

The basic syntax for creating an index in MySQL is:

Creating an index:

CREATE INDEX index_name ON table_name (column1, column2, ...);

Creating a unique index:

CREATE UNIQUE INDEX index_name ON table_name (column1, column2, ...);

Dropping an index:

DROP INDEX index_name ON table_name;

Example

Let’s go through an example where we create indexes on a table and then demonstrate their usage.

Full Example

1. Create a Database:

CREATE DATABASE company;

2. Select the Database:

USE company;

3. Create the Employees Table:

CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    employee_code VARCHAR(10) UNIQUE,
    department_id INT
);

4. Insert Data into the Employees Table:

INSERT INTO employees (first_name, last_name, email, employee_code, department_id) VALUES
('Rahul', 'Sharma', 'rahul.sharma@example.com', 'E001', 1),
('Priya', 'Singh', 'priya.singh@example.com', 'E002', 2),
('Amit', 'Kumar', 'amit.kumar@example.com', 'E003', 1),
('Neha', 'Verma', 'neha.verma@example.com', 'E004', 3),
('Sahil', 'Mehta', 'sahil.mehta@example.com', 'E005', 2);

5. Create an Index on the last_name Column:

CREATE INDEX idx_last_name ON employees (last_name);

6. Query the Employees Table:

Let’s perform a query to see how the index on the last_name column improves performance.

SELECT * FROM employees WHERE last_name = 'Singh';

Output

In this example, the index idx_last_name helps speed up the retrieval of rows where the last_name is ‘Singh’.

Adding an Index to an Existing Table

Let’s assume we have a table projects and we want to add an index to it.

1. Create the Projects Table:

CREATE TABLE projects (
    project_id INT PRIMARY KEY AUTO_INCREMENT,
    project_name VARCHAR(100) NOT NULL,
    project_code VARCHAR(10),
    department_id INT
);

2. Add an Index to the Projects Table:

CREATE INDEX idx_project_name ON projects (project_name);

3. Insert Data into the Projects Table:

INSERT INTO projects (project_name, project_code, department_id) VALUES
('Project A', 'P001', 1),
('Project B', 'P002', 2),
('Project C', 'P003', 1),
('Project D', 'P004', 3);

4. Query the Projects Table:

Let’s perform a query to see how the index on the project_name column improves performance.

SELECT * FROM projects WHERE project_name = 'Project B';

Output

In this example, the index idx_project_name helps speed up the retrieval of rows where the project_name is ‘Project B’.

Dropping an Index

If an index is no longer needed, it can be dropped to save storage space and eliminate overhead on data modification operations.

Dropping the Index on the last_name Column:

DROP INDEX idx_last_name ON employees;

Dropping the Index on the project_name Column:

DROP INDEX idx_project_name ON projects;

Important Considerations

• Performance Improvement: Indexes significantly improve the performance of data retrieval operations, especially on large tables.
• Storage Overhead: Indexes require additional storage space. The more indexes a table has, the more storage it will consume.
• Data Modification Overhead: Indexes add overhead to data modification operations (INSERT, UPDATE, DELETE) because the index needs to be updated along with the data.
• Index Selection: Carefully select columns for indexing based on the most frequently used queries to get the best performance improvement.

Conclusion

Indexes are a powerful feature in MySQL for improving the speed of data retrieval operations on a table. This chapter covered the syntax for creating, using, and dropping indexes, provided a full example with single and composite indexes, and discussed important considerations.