Home / Indexes / Question No: 137

I want to use MySQL multi column index feature but I am not much clear about it and need to learn it. Can you explain, 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-columns) specifically against those we would like to make  search. 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.

Multiple-column indexing is recommended when you know that a number of specified columns will often be used together in retrieval queries.  MySQL’s multiple-column indexing approach is based upon a strategy known as leftmost prefixing. Leftmost prefixing states that any multiple-column index including columns col1, col2, and col3 will improve performance on queries involving the following column combinations:

• col1, col2, col3
• col1, col2
• col1

To understand more about multi-column index you can visit, when does multi column index come into use? and how to create multi column index or key?