Updated May 23, 2023
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.
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 –
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 –
Let us search for the knack word’s meaning –
explain select meaning from dictionary where name = 'knack';that provides the following output –
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 –
explain select meaning from dictionary where name = 'knack';that provides the following output –
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 –
To verify if our indexes are properly added, execute the following command.
show indexes in educba_learning;That gives the following output –
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;
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 –
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 –
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.
