MySQL 8 Administrator???s Guide

Multiple-column indexes

MySQL 8 allows you to use multiple columns in a single index creation, which is also known as a composite index. It permits up to 16 columns in a composite index. At the time of composite index use, make sure you follow the same order of columns which was mentioned during index creation. A multiple-column index contains values generated by concatenating the values of indexed columns. Consider the following example in order to understand multiple-column indexes:

CREATE TABLE Employee (id INT NOT NULL,lastname varchar(50) not null,firstname varchar(50) not null,PRIMARY KEY (id),INDEX name (lastname, firstname));

As described above, we have defined composite indexes using two columns, lastname and firstname. The following ...