MySQL Add Index

Introduction to MySQL Add Index

We can create indexes on the table that help in optimizing the search of the records from the table while retrieving the specific results using SQL query. Mysql has to scan all the rows one by one to search for the particular record if none of the indexes is defined on that table. If the restriction that we have specified on the column in where clause has an index defined on the same column then there is no need for MySQL to search all the records of the table.

We have two types of indexes in Mysql. the primary index that is stored along with the data in the same table. Whenever we create a primary key or the unique key in the table and index with the PRIMARY is automatically created. The primary index is also referred to as the clustered index. The clustered index makes sure that the order is maintained in the storage of the data depending on the contents of columns that define this clustered or primary key. Besides the primary index, all other indexes defined in the table are referred to as secondary or non-clustered indexes.

In this article, we will learn about the indexes, their usage, optimization in retrieval and overhead of storage due to indexes and how we can add the indexes to the tables in MySQL while creating the table and even when the table already exists and index needs to be added along with their syntaxes and examples.

Indexes in MySQL

Let us understand the use of indexes by considering a simple example of a dictionary. Whenever we have to search a word in dictionary say knack, we firstly go for considering the first character of the word and as the dictionary is ordered alphabetically, it is easy for us to search for words beginning with k and then further search for the knack word in them. Similarly, while storing the records in the table with name and their meaning, if we query for the search of knack word’s meaning then we will have to search for all the records in our table.

However, if there is an index defined in the table on the column name then only a single record with knack word will be scanned while retrieving its meaning. Hence, while defining the indexes in the table we should first analyze which columns the restrictions will be applied in where clause of the queries that will be executed in the future. Like, in our case the query will be fired with the value specified on the name column to retrieve its meaning from a dictionary table.

Let us create a table named dictionary –

CREATE TABLE dictionary(
name VARCHAR(100),
meaning varchar(5000)
);

that gives following output –

Let us insert some records using the following query statement-

INSERT INTO
dictionary(name,meaning)
VALUES
('antonym','a word that means the opposite of another word'),
('connotation','an additional idea or emotion that a word suggests to you, in addition to its literal or main meaning'),
('etymology','he study of the origins of words; the origins of a particular word'),
('lexicography','the job or skill of writing dictionaries'),
('polysemy','the fact that some words can have more than one meaning'),
('thesaurus','a reference tool which shows groups of words that have similar meanings'),
('knack','an acquired or natural skill at doing something.'),
('flair','stylishness and originality.'),
('panache','a tuft or plume of feathers');

that gives following output –

Let us search for knack word’s meaning –

explain select meaning from dictionary where name = 'knack';

that gives the following output –

We can see that 9 rows needed to be scanned for retrieving the records.

Let us add the index on the name column and explain the query again.

CREATE INDEX on_name ON dictionary(name);

that gives the following output –

explain select meaning from dictionary where name = 'knack';

that gives the following output –

Now, we can see that only a single row is scanned to retrieve the same output. This is the benefit of using the indexes.

The indexes make the select operation fast but the update and insert operation become slower as along with the record to be inserted or updated the index on the table is also affected and modified while writing operation.

Adding Index While Creating the Table

We can specify the index, primary as well as secondary at the time of table creation itself by using the following syntax –

CREATE TABLE table_name(
column1 datatype PRIMARY KEY,
column2 datatype,
column3 datatype,
column4 datatype, ...
INDEX (column2,column3)
);

In the above syntax, we can see that there is one primary index defined on the primary key on the column named column1 and other secondary indexes in columns column2 and column3 collectively.

Let us have one example and create a table named educba_learning with columns topic_id, stream, subject, sessions, and expert_name. We will create two indexes, primary index on topic_id column, and secondary index on column subject while creating the table. Our query statement will be as follows –

CREATE TABLE educba_learning(
topic_id int PRIMARY KEY,
stream varchar(50),
subject varchar(100),
sessions int,
expert_name varchar(100),
INDEX (subject)
);

that gives following output –

To check whether our indexes are added properly execute the following command –

show indexes in educba_learning;

that gives the following output –

Let us now see how we can add an index on the existing table using the CREATE INDEX statement.

Syntax –

CREATE INDEX name_of_index ON name_of_table (list_of_columns);

We can give the name to the index we are creating by specifying the name at name_of_index. Further, we need to specify the name of the table on which the index is to create and the list of names of columns on which the index is to be defined.

Consider one existing table named Used_technologies which shows the following structure when described using below query –

desc Used_technologies;

Now, to add an index on name column with index name as index_on_name we can use CREATE INDEX statement in the following way –

CREATE INDEX index_on_name ON Used_technologies (name);

that gives the following output –

We can see all the keys defined on the  Used_technologies table using the following query –

show indexes in Used_technologies;

that gives following output –

We can see that our index named index_on_name is created successfully.

Conclusion

We can create the primary or secondary indexes on tables in Mysql while table creation or even after that using create index statement. Indexes greatly affect the fetching time when a large number of records are to be scanned and the index is defined on the column on which the restriction is applied in our select query for retrieval.

Recommended Articles

This is a guide to MySQL Add Index. Here we discuss the index, usage, optimization in retrieval, and overhead of storage due to index and how we can add the index to the tables in MySQL. You may also look at the following articles to learn more –

1. MySQL Subquery
2. ANY in MySQL
3. SQL CASE Statement
4. PostgreSQL log