Create Index in MySQL: MySQL Index Tutorial [2024]

A Data Scientist or a programmer will have to work with large quantities of data and needs to be able to deal with it efficiently for faster execution. They also need to know how the data is organised and the fastest methods to easily access the particular data needed.

Since MySQL is a relational database management system that has applications in various sectors such as web databases, e-commerce applications, social media etc. data scientists need to know all the techniques related to MySQL, one of which is indexes. The article describes how to create Index in MySQL.

Check out our free courses to get an edge over the competition.

What is an Index?

In MySQL, Indexes are used to sort data in an organised sequential way and retrieve the data efficiently and quickly. They are created by using one or more columns that will be used to filter data. Indexes are a type of table that keep a primary or index field and a pointer to each record in the main table.

For a better understanding, a book index can be used as an example. Instead of looking for a page by going through all the pages in a book, the book index can be checked to find the page one is looking for.

In most database systems, high and fast performance is needed. Many businesses invest huge sums of money on hardware for faster data retrievals and manipulations. But by optimizing the database, the costs can be reduced.

Response time is usually longer because records are stored randomly, and search queries have to find the needed data by looping through the entire stored records. Indexes make this search easier and faster.

Check out upGrad’s Advanced Certification in Cyber Security 

Read: Rename Column name in SQL

Create Index

You can create index in MySQL on a table by using the following basic syntax.

CREATE INDEX index_name table_name (column_name);

You can also create a unique index on a table. This means that the name is unique, and any two rows cannot have the same name. The following syntax can be used for this.

CREATE UNIQUE INDEX index_name ON table_name (column_name);

If a secondary index for a column is created, MySQL stores the values in a separate data structure such as B-Tree and Hash. In MySQL, if the columns are string columns, you can create an index for the leading part of the column values of the string column using the syntax:

column_name(length)

To create the column prefix key part when the table is created, the following syntax can be used:

CREATE TABLE table_name(

column_list,

INDEX(column_name(length))

);

And to add an index to an existing table:

CREATE INDEX index_name ON table_name (column_name(length));

The length here is the number of characters if it is a non-binary string type like CHAR, VARCHAR and TEXT and the number of bytes if it is binary string type like BINARY, VARBINARY and BLOB. You can also create column prefix key parts for CHAR, VARCHAR, BINARY, and VARBINARY columns in MySQL. It is necessary to specify the column prefix key parts if you have created indexes for BLOB and TEXT columns.

Check out upGrad’s Advanced Certification in Cloud Computing 

In case of FULLTEXT indexes that are only supported for InnoDB and MyISAM tables, they can include only CHAR, VARCHAR and TEXT columns. It does not support column prefix indexing and if any prefix length is specified, it is ignored.

Must Read: SQL Project Ideas

Altering tables to add or drop Index

To add indexes to a table, there are four types of statements that can be used:

1. To add a primary key:

ALTER TABLE table_name ADD PRIMARY KEY (column_list);

The indexed values here should be unique and cannot be NULL.

2. For an index with unique values:

ALTER TABLE table_name ADD UNIQUE index_name (column_list);

The values should be unique, except for NULL which can appear multiple times.

3. To add an ordinary index:

ALTER TABLE table_name ADD INDEX index_name (column_list);

Here the values can appear more than once.

4. To create special FULLTEXT index:

ALTER TABLE table_name ADD FULLTEXT index_name (column_list);

The FULLTEXT index can be used for text searching purposes.

In existing tables

To add index in any existing table, the following syntax can be used:

ALTER TABLE table_name ADD INDEX (index_name);

To delete an index in a table, the DROP INDEX statement is used:

ALTER TABLE table_name DROP INDEX (index_name);

Display Index Information

To list all the indexes associated with any table, the SHOW INDEX command is used.

SHOW INDEX FROM table_name\G

The ‘\G’ is used to create the list in a vertical format, this avoids the long line wraparound.

Also Read: SQL Interview Questions & Answers

Descending Indexes

InnoDB can store the entries in descending order when the index is a descending index. So, when the descending order is requested in the query, the optimizer chooses this index. This is more efficient for queries with ORDER BY clauses. These are only supported by the InnoDB storage engine.

The syntax for creating or adding descending indexes is like alter or create syntaxes used above.

 ALTER TABLE table_name ADD index_name (col1_name desc, col2_name asc);

CREATE INDEX index_name ON table_name (col1_name desc, col2_name asc);

upGrad’s Exclusive Software Development Webinar for you –

SAAS Business – What is So Different?

Invisible Indexes

To mark indexes as unavailable for the query optimizer, invisible indexes can be used. MySQL updates invisible indexes when the data in columns associated with the index changes. Indexes are visible by default and to make them invisible, you have to explicitly declare the visibility at the time of creation or by using the ALTER TABLE command.

To create an invisible index, the following syntax is used:

CREATE INDEX index_name ON table_name (c1, c2…) INVISIBLE;

To change the visibility of existing indexes, the following syntax in used:

ALTER TABLE table_name ALTER INDEX index_name [VISIBLE | INVISIBLE];

Enrol in Online Software Development Courses from the World’s top Universities. Earn Executive PG Programs, Advanced Certificate Programs, or Masters Programs to fast-track your career.

Conclusion

The performance of MySQL search queries can be improved by using Indexes. These can be defined when a table is created or can be added later if the table is already created.

This article gave a brief description of what an index is and why it is used. The next section explains how to create index in MySQL, how it can be added to an existing table and how to create a unique index.

The different commands to alter tables and create or drop an index were also described along with displaying index information. Descending and invisible indexes were also briefly explained. 

All in all, learning and getting hands-on practice on all the databases mentioned in this article will add to your experience. Go through the course overview, learning, and job opportunities involved with it—platforms like upGrad offer power-packed courses designed by the highly reputed institutes like IIIT-B in Executive PG Program Full Stack Development.