Multi-Value Index in MySQL

Creating Multi-Value Index

We can create a multi-value index while creating a table, as well as in some existing tables. Let us discuss both methods one by one.

While Creating a Table

We can create a multi-value index at the time of table creation. In order to create a multi-value index, we need to write the INDEX keyword followed by the index name and columns for which we want to create an index. 

Syntax

CREATE TABLE table_name (
Column1 data_type PRIMARY KEY,
Column2 data_type ,
Column3 data_type ,
INDEX index_name (column1, column2)
);

 

Here, the above syntax would create a table and a multi-value index with column1 and column2 using the INDEX keyword. 

In Existing Table

We can create a multi-value index in an existing table using the CREATE INDEX statement in MySQL. Just like in the previous example, here, you also need to write the index name and the columns for which the index is to be created.

Syntax

The basic syntax to create a multi-value index is:-

CREATE INDEX index_name ON table_name (column1, column2);

 

The syntax to create a unique multi-value index is:-

CREATE UNIQUE INDEX index_name ON table_name (column1, column2);

 

Description of syntax:

• CREATE INDEX: It creates a new index in MySQL.
   
• UNIQUE: It prevents duplicate index creation.
   
• index_name: It is the name of the index which we want to create.
   
• table_name: It is the name of the table on which the index is to be created.
   
• column1, column2: These are the names of the columns on which the index is to be created.
   

Example

Let’s now create a multi-value index on a table ‘student’ in our school database. Below is what our database looks like:

Suppose we want to create an index for the Name and City columns from our table. Then we can use the below query to get the result:

CREATE INDEX Name_City ON student(Name, City);

 

This statement will create an index (Name_City) without changing any data in our table. To verify that index is successfully created, use the below syntax:

SHOW indexes FROM table_name;

 

The output after executing the above statement is:

Thus, we have successfully created an index for columns Name and City.

Using Mult-Value Index in Queries

There is no change of syntax whenever we use the indexed columns in our queries. It just boosts the execution speed and gives results quickly. In our student table, we have a multi-value index on columns Name and City. Now, let’s look at one example where we will use these columns in our query.

Example

Suppose we want to get students who are from Pune city in our student table having a multi-value index on columns Name and City. In this case, we can write the below query to get our result.

SELECT * FROM student WHERE city='Pune';

 

Output

Explanation

In the above example, we retrieved the data where the city is Pune. This process is super fast, and it gives our results in very less time compared to normal queries.

Altering a Table with Multi-Value Index

We can alter our table with multi-value indexes in MySQL. Below are some modifications which we can perform:

Adding a new column to the index:

ALTER TABLE table_name ADD INDEX index_name (column1, column2, New_column);

 

This will add a new column to our existing multi-value index on column 1 and column 2.

Dropping a multi-value index:

ALTER TABLE table_name DROP INDEX index_name;

 

The above statement will drop the multi-value index.

Advantages of Multi-Value Index

Below are some of the pros of using multi-value indexes in MySQL.

• A multi-value index allows efficient querying and sorting on multiple columns simultaneously by using the pre-sorted order of indexes and narrowing the search for the relevant rows.
   
• It can reduce the disk input/output operations by retrieving only the necessary index pages rather than retrieving the entire table.
   
• A multi-value index combines the index columns into a single index structure, thus optimising as well as reducing the storage space needed for indexes.
   
• In a multi-value index, a single index is created for coverage of multiple columns; thus, it eliminates the need for creating separate indexes for each column.
   
• Due to a single composite index, it is very easy to manage indexes in a multi-value index.

Disadvantages of Multi-Value Index

Below are a few cons of the multi-value index in MySQL.

• As a multi-value index uses multiple columns, it can result in a larger index size compared to the single-column index.
   
• Due to its large size, it is difficult and complex to manage a multi-value index.
   
• A multi-value index is not useful when required to query over a table with a small data size.
   
• It can slow down the data modification operations due to the updation of the index each time.
   
• Fragmentation can reduce the Indexing performance and increase disk I/O Operations.

Single-Value Index vs Multi-Value Index

Below is the difference between a single-value index and a multi-value or composite index in MySQL.

Single-Value Index	Multi-Value Index
A single-value index is built on a single column.	A multi-value index is built on multiple columns.
The single-value index is generally smaller in size.	The multi-value index is larger in size.
It is mainly used in optimising queries, which involve filtering, sorting or grouping single columns from the table.	It is mainly used in queries involving multiple columns to improve the performance of joins and where conditions.
It is easy to create a single-value index in MySQL.	Creating a multi-value index is slightly more complex than creating a single-value index.
It is faster to update a single-value index during insertion and deletion operations.	A multi-value index is slower to update during insertion and deletion operations.

Frequently Asked Questions

What is a multi-value index in MySQL?

In MySQL, a multi-value or composite index is an index created on two or more columns of a table.

When should we avoid using multi-value indexes?

We should avoid using multi-value indexes when the table has less number of entries, columns are updated frequently, and index columns are not often used in queries.

How many types of indexes are available in MySQL?

There are three types of indexes in MySQL which are single-column, Unique, and multi-column (composite).

What is the syntax to drop a multi-value index in MySQL?

The syntax to drop a multi-value index is ALTER TABLE table_name DROP INDEX index_name;

Conclusion

This article discusses the concept of multi-value index in MySQL. We discussed how to create, use and drop a multi-value index. We hope this blog has helped you enhance your knowledge of the multi-value index in MySQL. If you want to learn more, then check out our articles.

• Index Statement in MySQL
• Indexing in DBMS
• MySQL Storage Engine

 

Refer to our Guided Path to upskill yourself in DSA, Competitive Programming, JavaScript, System Design, and many more! If you want to test your coding ability, you may check out the mock test series and participate in the contests hosted on Coding Ninjas!

But suppose you have just started your learning process and are looking for questions from tech giants like Amazon, Microsoft, Uber, etc. In that case, you must look at the problems, interview experiences, and interview bundles for placement preparations.

However, you may consider our paid courses to give your career an edge over others!

Happy Learning!