MySQL Index: Create Index

By /
MySQL Index Create Index

The building block of any database management system (DBMS) is managing the data, in Relational-DBMS it is managing records within tables and tables within a database. MySQL is also one of the many RDBMS currently used by a huge number of users and organizations.

MySQL provides many features and functionality to manage, control, and maintain the tables for fast processing and data retrieval. One of the many methods is the Indexing Data Structure. Indexing is used for fetching data in a faster and more efficient manner.

In this tutorial, we’ll see how to create an index while creating a new table and we’ll also create an index for a table that already exists.

Why do we need indexing?

As we have discussed briefly indexing in the introduction, in this section we’ll see a few reasons why indexing is important for any database and why is it needed:

  1. Fast retrieval of data: Let’s take an example to understand it properly, when we are working with tables that have a large number of record sets, retrieval of data can be difficult as each and every record will be iterated to fetch the relevant information. Indexing is done on a particular column or group of columns which makes the retrieval faster.
  2. Improves performance: Indexing helps in improving the overall performance of the database system, as faster retrieval and faster access help in reducing the computational cost.

MySQL CREATE INDEX statement

Generally, when we have a primary key for a table, it is considered an index in MySQL. But in case, when we do not have a primary key for a table, then an index is created. Indexes can be created at the time of creating the table or later as well.

The syntax for Creating Index when a table is being created:

CREATE TABLE table_name(
field1 datatype,
field2 datatype,
field3 datatype,
field4 datatype,
INDEX(field1,field4);Code language: SQL (Structured Query Language) (sql)

The syntax for Creating an Index:

CREATE INDEX index_name ON table_name(column_name);Code language: SQL (Structured Query Language) (sql)

Examples for creating an index

In this section, we’ll see a few examples, in which we will create indexes for tables and we will also see how to view indexes for a particular table.

Let’s take an example, we are going to create a table for all the residents residing in a building. We will save fields such as first name, last name, contact number, house number, and age. Now, some of these residents might share the same house, they might also share the same phone numbers, therefore, it is difficult to make a primary key. In cases like these, we can create INDEX.

We will create an index that will consist of first name and house number. Let’s see the query below:

CREATE TABLE residents(
firstname VARCHAR(30),
lastname VARCHAR(30),
contact INT(10),
house_number INT(4),
age int(3),
INDEX(firstname, house_number)
);Code language: SQL (Structured Query Language) (sql)

Now let’s take another example, What if we already have an existing database or residents only, but initially we did not create any index for the table. The fields are as follows- first name, last name, contact number, house number, and age. We have to create an index for the same table but the constraint is that it already exists.

To do that let’s see the below command:

CREATE INDEX index_person ON residents(firstname, house_number);Code language: SQL (Structured Query Language) (sql)

To check the indexes for a particular table, in our case residents table, you can use the below command:

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

To see all the indexes for all the tables present within a database, you can use the below command:

SELECT DISTINCT
    TABLE_NAME,
    INDEX_NAME
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = 'database_name';Code language: SQL (Structured Query Language) (sql)

Conclusion

In this tutorial, we learned about indexes and how we can work with them. We understood how it works and how can we create an index by ourselves. We also learned to check the indexes for a particular table as well as for a particular database.