MySQL Indexing – A Complete Guide

By /
MySQL Indexing

In this tutorial, we’ll understand what’s indexing, why indexing is used in SQL, the types of indexing, and when to use indexing.

Introduction

Indexing is used for faster access to data in a table or tables rather than using the normal way of searching the whole table.

To understand the topic better let’s take an example, throughout our lives we read a variety of books from fiction to college publications but one thing which is common in all these books is the index. Indexes are important in books as it helps in searching the topics very easily. Just like dictionaries have alphabetical order of letters with marked areas for all the letters separately. In the same manner, SQL also uses the concept of the index.

In MySQL indexing is used for faster retrieval of data than otherwise. Indexing is used for tables with a large set of data. Also, MySQL automatically creates an index for the foreign key in a table for faster retrieval of data in that table without going through all the values of the table.

Why is indexing used in DBMS?

DBMS uses indexing for the following reasons:

  • Faster retrieval of data: When data is huge then going through each and every record will be a time-consuming activity for the CPU, therefore indexing comes into play to categorize the data. Let’s assume that we have a student table and there are 1,00,000 records of students, we want to fetch the data of students whose name starts with the letter Z. Now, if you don’t have an index for the table then it would table time to find all the student’s records but if the table is indexed then it would be way faster.
  • Reduced input/ output cost: When the table is the indexed cost of output is reduced, the cost of output refers to nothing but the time which is being consumed to do a particular task from the memory. In SQL’s case, it’s retrieving the data according to the query fired by the user.

Index syntax where duplicate values are allowed:

CREATE INDEX index_name
ON table_name (column1, column2, ...);Code language: SQL (Structured Query Language) (sql)

Index syntax where duplicate values are not allowed:

CREATE UNIQUE INDEX index_name
ON table_name (column1, column2, ...);Code language: SQL (Structured Query Language) (sql)

When we want to understand the internal working of a SELECT query or we want to know if the table is indexed or not then we can use the below query:

 EXPLAIN SELECT field1, field2 FROM tableName WHERE field = 'value'; Code language: SQL (Structured Query Language) (sql)

Types of indexing

There are three types of indexes in DBMS:

  • Primary
  • Cluster
  • Secondary

Primary Index

A primary index is an index that is created when the data in a table satisfies two conditions:

  1. Ordered Data: This means that the data in the table is ordered either in ascending or descending order. For example, if the roll number of students is in ascending order (1,2,3,4,… so on) then it is ordered data.
  2. Key Values: Key values mean that the data must be unique, i.e., No repetition of the same data again and again. For example, the roll number of students, the roll numbers are unique to each and every student so that they could be identified uniquely, and hence there is no redundancy in roll numbers.

If data is both Ordered and has a Key then the primary index is used.

Cluster Index

A clustered index is an index that is to be created when the data satisfies the given conditions:

  1. Ordered data: Order data is data that is sorted either in ascending or in descending.
  2. Non-key: Non-key values are those values that or not unique. For example, names in a table are not unique and can be repeated.

If data is Ordered but has non-key values then the clustered index is used.

Secondary Index

A secondary index is an index that is to be created when the data is in one of the two mentioned conditions:

  1. Unordered with Key Value: This means that the data is neither in ascending nor descending order but each and every data can be found uniquely. For example, list of packaging for an online company, each and every package has a unique id but the ids are not ordered in ascending or descending fashion.
  2. Unordered without Key Value: This means that the data is neither sorted in any order nor contains any unique data. For example, a list of clothes doesn’t have unique values neither it is ordered.

If data is unordered as well as has key or non-key values then the secondary key is used.

How to create and remove an index from an existing table?

To create an index on an existing table we can use ALTER table query:

ALTER TABLE tableName ADD INDEX indexName (columnName);Code language: SQL (Structured Query Language) (sql)

To remove an index from an existing table we will use the DROP query:

ALTER TABLE tableName DROP INDEX indexName;Code language: SQL (Structured Query Language) (sql)

You can also view the index of a table using the SHOW query:

SHOW INDEX FROM tableName;Code language: SQL (Structured Query Language) (sql)

Conclusion

In this tutorial we learned about indexing, the need for indexing, the different types of indexing, the syntax of indexing, creating an index using CREATE and ALTER query, creating an index as well as removing an index, and at last view an existing index.