MySQL CREATE Index

Introduction

In this chapter, we will learn about creating indexes in MySQL. Indexes are database objects that improve the speed of data retrieval operations on a table. By creating indexes on columns that are frequently used in queries, you can significantly enhance the performance of those queries. 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 a Regular Index:

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

Creating a Unique Index:

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

Creating a Full-Text Index:

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

Creating a Spatial Index:

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

Example

Let’s go through an example where we create different types of indexes on a table and 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 a Regular Index on the last_name Column:

CREATE INDEX idx_last_name ON employees (last_name);

6. Create a Unique Index on the employee_code Column:

CREATE UNIQUE INDEX idx_employee_code ON employees (employee_code);

7. Query the Employees Table:

Let’s perform a query to see how the indexes improve 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’.

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 indexes with examples, and discussed important considerations. In the next chapter, we will explore how to DROP indexes.