What are indexes in MySQL

This article will discuss indexes in MySQL and how to create them, and what difference they make to queries dealing with tables with vast amounts of data.

Table of Contents:-

• Introduction to MySQL indexes
• Where do we use indexes
• Types of indexes in MySQL
• MySQL add index while creating a table
• MySQL create index using CREATE statement
• MySQL add index using ALTER statement
• MySQL index examples

Introduction to MySQL indexes

Indexes or indices are used to get results rapidly from any column or a combination of columns in a table with specific data. If we do not apply any index, MySQL will begin scanning from the first row and go all the way to the last row until it finds the relevant row with data. Let us say we want only 1 row from millions of records, then the cost to scan each row and find the required one becomes high. Therefore it does not makes sense to search sequentially every time, and hence indexes are widely used in MySQL databases.

MySQL uses BTREE data structure to store indexes. Memory tables in MySQL support hash indexes.

Where do we use indexes

Since indexes find the data quickly, they are used generally:

• To find the data from queries with WHERE clause.
• To eliminate some rows from consideration in queries or sub-queries.
• Indexes can be applied to multiple columns for query optimizations.
• To retrieve data from other tables in JOIN queries.
• To find min value or max value from indexed columns.

Types of indexes in MySQL

There are two types of indexes:

• Clustered Index: In InnoDB table has a particular index called the clustered index. Whenever a key is defined as a PRIMARY key, an index is created automatically on it. In case there is no primary key defined, the first UNIQUE key is indexed. If both PRIMARY and UNIQUE are not present, a hidden clustered index is created on a column with row id values.
• Secondary Index: All the indexes apart from the clustered index are secondary indexes.

NOTE: We can create PRIMARY, UNIQUE, ORDINARY, and FULLTEXT indexes. Read more about these indices: MySQL how to add an index to the existing table. 

MySQL create index while creating a table

In this section, let us create the index while creating a table named sale_details. We are adding the index on two columns, no_products_sold, and sales_department.

CREATE TABLE sale_details (
    id INT ,
    sale_person_name VARCHAR(255),
    no_products_sold INT,
    sales_department VARCHAR(255),
    INDEX(no_products_sold,sales_department)
);

To verify if the index got created or not execute:

SHOW INDEX FROM sale_details;

Output:-

Output in image_1 shows that the index is created on no_products_sold and sales_department.

MySQL create index using CREATE statement

Observe the below query to create an ordinary index on column sale_person_name

 CREATE INDEX index_on_sale_person_name ON sale_details(sale_person_name);

To verify if the index got created or not execute:

SHOW INDEX FROM sale_details;

Output:-

MySQL add index using ALTER statement

Alternatively, if we want to create an index on an already existing MySQL table, we can use ALTER statement. Observe the below query to add the same ordinary index on column sale_person_name as in the previous section. We will first drop the index index_on_sale_person_name and recreate it using ALTER statement.

DROP INDEX index_on_sale_person_name ON sale_details;
ALTER TABLE sale_details ADD INDEX index_on_sale_person_name (sale_person_name);

Output:-

Let us verify if the index got created or not execute:

SHOW INDEX FROM sale_details;

Output:-

The output is the same as shown in image_2.

READ MORE: how to create different types of indexes on already existing tables.

MySQL index examples

Let us now see what difference it made on our table sale_details after creating indexes. To understand the concept, we will be inserting some rows into it.

 INSERT INTO sale_details (id,sale_person_name,no_products_sold,sales_department) 
 VALUES(1,"Henry",2000,"Kitchen Essentials"),
 (2,"Henry",5000,"Apparels"),
 (3,"Henry",40,"Medicines"),
 (4,"Richa",3000,"Kitchen Essentials"),
 (5,"Richa",500,"Apparels"),
 (6,"Richa",50,"Medicines"),
 (7,"Ved",100,"Kitchen Essentials"),
 (8,"Ved",150,"Apparels"),
 (9,"Ved",1000,"Medicines"),
 (10,"George",600,"Kitchen Essentials"),
 (11,"George",1200,"Apparels"),
 (12,"George",200,"Medicines"),
  (13,"Alexendra",487,"Kitchen Essentials"),
 (14,"Alexendra",570,"Apparels"),
 (15,"Alexendra",510,"Medicines"),
 (16,"Gustav",3000,"Kitchen Essentials"),
 (17,"Gustav",100,"Apparels"),
 (18,"Gustav",150,"Medicines");

Since we created an index in the previous section on no_products_sold and sales_department columns, we will write a query to get the sales details where no_products_sold =100 and sales_department = Kitchen Essentials.

SELECT * FROM sale_details WHERE no_products_sold = 100 AND sales_department = "Kitchen Essentials" ;

Output:-

There is only a single record with the above criterion. We will do an EXPLAIN on this select statement.

EXPLAIN SELECT * FROM sale_details WHERE no_products_sold = 100 AND sales_department = "Kitchen Essentials" ;

Output:-

Output in image_6 shows that the MySQL server did not have to scan all the 18 records of the table to get the required result but just 1. To add more clarity, let us DROP the index no_products_sold and again do EXPLAIN on select to see the difference.

DROP INDEX no_products_sold ON sale_details;
EXPLAIN SELECT * FROM sale_details WHERE no_products_sold = 100 AND sales_department = "Kitchen Essentials" ;

Output:-

Output in image_7 shows that MySQL scanned all the 18 records of the table to get the results.

READ MORE:

• MySQL create table if not exists
• MySQL ADD COLUMN IF NOT EXISTS
• MySQL get column names

We hope this article helped you to build an understanding of indexes in MySQL. Good Luck !!!