How to create index for existing table in MySQL

In this tutorial, we are going to see how to create an index for an existing table in MySQL.

Suppose you have a contact list of people in your family. And you want to find the phone number of Thomas Calves. Considering that the names are in alphabetical order, you start by looking for the page where the name is Thomas Calves, and then his phone number.
 

 
 
Now, if the names are not sorted alphabetically, you will have to go through all the pages, reading each name until you find Thomas Calves. This is called a sequential search. You go through all the entries until you find the person with the phone number you are looking for.

By associating the contact list with a database table, if you have a contact table and you need to find the phone number of Thomas Calves, you would run the following query:

SELECT
    phoneNumber
FROM 
    contacts
WHERE 
    name = 'Thomas Calves';

It’s quite easy. Although the database has to scan all rows of the table until it finds the row. If the table has millions of rows, with no indexes, it would take a long time to return the result.
 

How to create index for existing table in MySQL

Usually, you create indexes when you create a table. For example, the following statement creates a new table with an index consisting of two columns column2 and column3.

CREATE TABLE table_name(
   column1 INT PRIMARY KEY,
   column2 INT NOT NULL,
   column3 INT NOT NULL,
   column4 INT NOT NULL,
   INDEX (column2, column3) 
);

To add index for a column or a set of columns, use CREATE INDEX statement as follows:

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

For example, to add a new index to “name” column in “contacts” table, use the following statement:

CREATE INDEX index_name ON contacts(name);

MCQPractice competitive and technical Multiple Choice Questions and Answers (MCQs) with simple and logical explanations to prepare for tests and interviews.Read More