EDUCBA

EDUCBA

MENUMENU
  • Blog
  • Free Courses
  • All Courses
  • All in One Bundle
  • Login
Home Data Science Data Science Tutorials MySQL Tutorial MySQL Add Index

MySQL Add Index

Payal Udhani
Article byPayal Udhani
Priya Pedamkar
Reviewed byPriya Pedamkar

Updated May 23, 2023

MySQL Add Index

Introduction to MySQL Add Index

We can create indexes on the table that help optimize the search of the records from the table while retrieving the specific results using SQL query. If none of the indexes are defined on a table in MySQL, it has to individually scan all the rows to search for the particular record. If the restriction we have specified on the column in which clause has an index defined on the same column, then MySQL does not need to search all the table records.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

We have two types of indexes in Mysql. The data in the same table stores the primary index. Whenever we create a primary or unique key in the table, it automatically creates an index with the PRIMARY. We also refer to the primary index as the clustered index. The clustered index maintains the order in the data storage based on the contents of columns that define this clustered or primary key. We refer to all indexes defined in the table, except for the primary index, 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 dictionary example. Whenever we have to search for a word in the dictionary, say knack, we first consider the word’s first character. 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.

Defining an index in the table on the column name enables the scanning of only a single record with a matching keyword when retrieving its meaning. Therefore, when defining indexes in the table, we should first analyze which columns will have restrictions applied in the WHERE clause of future queries. In our case, we execute the query using the specified value 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 the following output –

mysql add index output 1

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 the following output –

mysql add index output 2

Let us search for the knack word’s meaning –

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

that provides the following output –

mysql add index output 3

We can see that nine rows must be scanned to retrieve the records.

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

CREATE INDEX on_name ON dictionary(name);

that gives the following output –

mysql add index output 4

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

that provides the following output –

mysql add index output 5

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 contribute to select operations faster, but they can slow down the update and insert operations. This is because, during the write operation, along with the record being inserted or updated, the index on the table is also impacted and modified.

Adding Index While Creating Table in MySQL

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)
);

Looking at the above syntax, we can observe that a primary index is defined on the primary key in the column named column1. We have collectively defined secondary indexes in columns 2 and column 3.

Let us have one example and create a table named educba_learning with columns topic_id, stream, subject, sessions, and expert_name. While creating the table, we will create two indexes, the primary index on the topic_id column and the secondary index on the column subject. 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 the following output –

output 6

To verify if our indexes are properly added, execute the following command.

show indexes in educba_learning;

That gives the following output –

output 7

Let us see how we can add an index to 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 create by specifying the name at name_of_index. Further, we need to select 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 the below query –

desc Used_technologies;

output 8

Now, to add an index on name column with the 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 –

output 9

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

show indexes in Used_technologies;

that gives the following output –

output 10

We can confirm the successful creation of the index named “index_on_name.”

Conclusion

We can create the primary or secondary indexes on tables in Mysql while creating tables or even after using create index statement. Indexes significantly impact the fetching time, especially when scanning a large number of records. To enhance the retrieval process, we should define the index on the column where we apply the restriction in our select query.

Recommended Articles

We hope that this EDUCBA information on “MySQL Add Index” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

  1. MySQL Subquery
  2. ANY in MySQL
  3. SQL CASE Statement
  4. PostgreSQL log
All in One Excel VBA Bundle
500+ Hours of HD Videos
15 Learning Paths
120+ Courses
Verifiable Certificate of Completion
Lifetime Access
Financial Analyst Masters Training Program
2000+ Hours of HD Videos
43 Learning Paths
550+ Courses
Verifiable Certificate of Completion
Lifetime Access
All in One Data Science Bundle
2000+ Hour of HD Videos
80 Learning Paths
400+ Courses
Verifiable Certificate of Completion
Lifetime Access
All in One Software Development Bundle
5000+ Hours of HD Videos
149 Learning Paths
1050+ Courses
Verifiable Certificate of Completion
Lifetime Access
Primary Sidebar
All in One Data Science Bundle2000+ Hour of HD Videos | 80 Learning Paths | 400+ Courses | Verifiable Certificate of Completion | Lifetime Access
Financial Analyst Masters Training Program2000+ Hours of HD Videos | 43 Learning Paths | 550+ Courses | Verifiable Certificate of Completion | Lifetime Access
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more