MySQL Database learning



Home / Indexes / When does multi column index come into use in MySQL?
Share this job:


When does multi column index come into use in MySQL?

I want to use MySQL multi column indexes feature but I need to know about that how to use them. Can you explain when does multi column index come into use?

Multi column index is a useful feature to optimize searches. MySQL allows to create an index either on single column or on multi columns of the table however single column index and multi column(combination of columns) index behavior differs. To understand the behavior of single column index and multi column index, we will walk through the following examples. I assume after this we will be able to decide when to use multi column index. To continue, let's create a table named "buyers" to take it as an example:

CREATE TABLE buyers(
buyer_id INT NOT NULL AUTO_INCREMENT,
first_name CHAR(19) NOT NULL,
last_name CHAR(19) NOT NULL,
zip CHAR(5) NOT NULL,
state_code CHAR(2) NOT NULL,
PRIMARY KEY (buyer_id)
);

A small portion of the data file (we inserted) may be represented like below:

buyer_id first_name last_name zip state_codess
[...] [...] [...] [...] [...]
991 zeshan Nadeem 92082 CA
992 Ken Marcus 92082 CA
993 Tariq Iqbal 92082 CA
994 Tariq Iqbal 92082 CA
995 Hasnat Ahmad 92083 NY
996 Tariq Iqbal 92082 DC
997 Keith Worlf 93083 NG
998 Ashley Lewis 92088 NJ
999 Tariq Mehmood 99088 TX
[...] [...] [...] [...] [...]

In the above table, there are four Tariqs (three Iqbals, one Mehmood), three from 92082 zip, and one from another zip 99088 (Tariq Mehmood).

Now our requirement is to get the buyer_id for buyers with a specific first_name, last_name, and zip. For example, we want to find the buyer_id for Tariq Iqbal, zip 92082 so we write the following query to the desired record:

SELECT buyer_id FROM buyers WHERE first_name='Tariq' 
AND last_name='Iqbal' AND zip=92082;

As per our above query and structure of exemplary defined table "buyers", MySQL will scan all the table three times to find the requested record. Of course this may take considerable time to find out wanted record particularly when the table has records in millions in it.

Since we want to ask MySQL to avoid a full table scan, therefore we would like to take advantage of indexes to get them in use. First option is to create an index on each column (so called "single column index") viz first_name, last_name, or zip. Let's we put the index on each column like below so that MySQL should skip to scan all table three times:

ALTER TABLE buyers ADD INDEX idx_firstname (first_name);
ALTER TABLE buyers ADD INDEX idx_last_name (last_name);
ALTER TABLE buyers ADD INDEX idx_zip (zip);

In this case at the first instance MySQL will use the idx_firstname index to limit the records to those where first_name='Tariq'. At the next step, using this "temporary result set" MySQL will apply other indexes conditions individually i.e. last_name='Iqbal' and zip=92082. First it eliminates those whose last_name is not Iqbal. And then it eliminates those who are not from zip 92082. MySQL has now applied all conditions and can return the results after more than one sorting.

Of course, the above is more efficient than forcing MySQL to do a full table scan, but we are still forcing MySQL to scan significantly more rows than it needs to.

Now here's where the multi column index comes into use. If we add a single index on the three columns, we can get the correct set in a single pass! However, here is the code we use to add multi column index (index on combination of columns):

ALTER TABLE buyers ADD INDEX idx_flname_zip(first_name,last_name,zip);

Since the MySQL keeps the index files in an organized versions, MySQL can jump directly to the correct first_name, then move to the correct last_name, and finally go directly to the correct zip. Therefore, MySQL has found the correct rows without having to scan a single row of the data file!

Thus creating three single-column indexes on (first_name), (last_name), and (zip) is completely different from one "multi column index" on (first_name, last_name, zip). While running a query, MySQL can only use one index. So, if we have three single column indexes instead of multi column index(first_name, last_name, zip), MySQL will attempt to pick the most restrictive one, but the most restrictive single column index will be significantly less restrictive than our multi column index.

Point to note about Multi Column Index

With the multi column index (first_name, last_name, zip) then queries can only use the index if you have a WHERE clause that partially matches the index from left to right. Check below:

1. [SELECT * FROM buyers WHERE first_name=? AND last_name=? AND zip=?] will use the index.
2. [SELECT * FROM buyers WHERE last_name=? AND first_name=? AND zip=?] can't use the index.





Indexes related other questions

What is index terminology or concept and how does index work in MySQL?

An INDEX is essentially an ordered (or indexed) subset of table columns, with each row entry pointing to its corresponding table row.We index the specific columns to keep them in organized versions. A ...

What is foreign key in MySQL?

FOREIGN KEY is used to establish the relationship among tables. FOREIGN KEY type can be Primary Key, ...

How to remove or drop indexes in MySQL?

MySQL allows to drop existing INDEXES and PRIMARY KEYs.  For this MySQL provides ALTER TABLE and DROP ...

What are advantages and disadvantages of indexes in MySQL?

Indexing is one of the more useful features of MySQL. MySQL allows several types of indexes like primary key index ...

What are best practices to pick columns to index?

Indexes can play an important role in query optimization and searching the results speedily from tables. So it is most important step to select which columns to be indexed. There are two major places where we ...

Is it possible to apply more than one keys on a single column?

Yes, it's possible that more than one keys can be applied to a given column. For example, a column that is a PRIMARY KEY or  any other type of KEY might also be part of other indexes. ...

How many types of indexes or keys are in MySQL?

MySQL allows four general types of indexes (keys). These indexes can be created either on single column or multi-columns however both ...

What is multi column index or key in MySQL?

Indexes is a useful feature to optimize searches.  We can make our SELECT queries faster using MySQL indexes. For this purpose, we create an index on single column or on combination of columns (multi-col ...

What is single column index or key in MySQL?

MySQL allows to create single column index and multi-column index to facilitate quick retrieval ...

What is partial-column and prefixed-column index or key in MySQL?

An  index is  used if a particular column in your  table will be the focus of a considerable number of your SELECT  queries. For example, suppose an buyer profile table consists of four co ...

When does multi column index come into use in MySQL?

Multi column index is a useful feature to optimize searches. MySQL allows to create an index either on ...

How to get existing indexes of the table in MySQL?

To view the keys or indexes on a table, MySQL provides the SHOW KEYS and SHOW INDEXES commands. You can use any of these commands. The result of both commands is the same. Take the following examples: ...