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.

How to create and drop an index in MySQL

Indexes speed up data retrieval in MySQL. Use CREATE INDEX for single or multi-column indexes and DROP INDEX to delete them.