How to create and drop an index in MySQL

An index is a database object that speeds up data retrieval from a table. Indexes are used to optimize the performance of SELECT queries, as they reduce the time and resources required to retrieve the data.

To create an index in MySQL, we can use the CREATE INDEX statement. There are two main types of indexes in MySQL:

  1. Single-column index
  2. Multi-column index

Single-column index

This type of index is created on a single column of a table. It is used when frequently searching for or sorting data based on that particular column.

Let’s look at the syntax of a single-column index:

CREATE INDEX index_name ON table_name (column_name);

Multi-column index

This type of index is created on multiple columns of a table. It is beneficial when the queries involve conditions on multiple columns.

Let’s look at the syntax of a multi-column index:

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

Let’s “Run” the following code below and see how the INDEX works:

-- Create database company
create database company;
use company;
-- Create employees table
CREATE TABLE employees (
    id INT PRIMARY KEY,
    first_name VARCHAR(40),
    last_name VARCHAR(40),
    department VARCHAR(40)
);
-- Creating a single-column index on the 'department' column
CREATE INDEX single_column ON employees (department);
-- Creating a multi-column index on the 'first_name,last_name and department' columns
CREATE INDEX multi_column ON employees (first_name,last_name,department);
-- Insert some sample data into the employees table
INSERT INTO employees (id, first_name, last_name, department)
VALUES
    (1, 'Jam', 'Doe', 'Sales'),
    (2, 'Dex', 'Smith', 'HR'),
    (3, 'Lee', 'Johnson', 'Finance'),
    (4, 'Jhon', 'Williams', 'IT'),
    (5, 'Robert', 'Brown', 'IT');
-- Show the details of single and multi column indexes
SHOW INDEXES FROM employees\G;

Code explanation

  • Lines 6–11: Create a table named employees with four columns: id, first_name, last_name, and department.

  • Line 14: This creates a single-column index named single_column on the department column of the employees table. This index will improve the performance of queries that involve filtering or sorting based on the department column.

  • Line 16: This creates a multi-column index named multi_column on the first_name, last_name, and department columns of the employees table. This index will be beneficial for queries that involve filtering or sorting based on combinations of these columns.

  • Lines 19–25: Insert data into the employees table.

  • Line 28: This line displays information about the indexes defined on the employees table. It will show details like the index name, column names in the index, index type, and some statistics related to the index.

Delete index

The DROP INDEX command deletes an index from a table.

Let’s say we want to drop the index named single_column and multi_column from the employees table. The query will be:

DROP INDEX single_column ON employees;
DROP INDEX multi_column ON employees;

Let’s execute these two lines and see the output:

-- Delete the single and multi column indexes
DROP INDEX single_column ON employees;
DROP INDEX multi_column ON employees;
-- Show the details of indexes
SHOW INDEXES FROM employees\G;
  • Line 2–3: The specified index will be removed from the table, and the associated data structure will be deleted.

Free Resources

Copyright ©2025 Educative, Inc. All rights reserved