MySQL может создавать составные индексы (то есть индексы по нескольким столбцам). Индекс может состоять максимум из 16 столбцов. Для определённых типов данных вы можете индексировать префикс столбца (см. Раздел 8.3.4, «Индексы по столбцам»).

MySQL может использовать индексы по нескольким столбцам для запросов, которые проверяют все столбцы в индексе, или запросов, которые проверяют только первый столбец, первые два столбца, первые три столбца и так далее. Если вы укажете столбцы в правильном порядке в определении индекса, один составной индекс может ускорить несколько типов запросов к одной и той же таблице.

Индекс по нескольким столбцам можно рассматривать как отсортированный массив, строки которого содержат значения, созданные путём конкатенации значений индексированных столбцов.

SELECT * FROM tbl_name
  WHERE hash_col=MD5(CONCAT(val1,val2))
  AND col1=val1 AND col2=val2;

Предположим, что таблица имеет следующее описание:

CREATE TABLE test (
    id         INT NOT NULL,
    last_name  CHAR(30) NOT NULL,
    first_name CHAR(30) NOT NULL,
    PRIMARY KEY (id),
    INDEX name (last_name,first_name)
);

Индекс name — это индекс по столбцам last_name и first_name. Индекс можно использовать для поиска в запросах, которые задают значения в известном диапазоне для комбинаций значений last_name и first_name. Он также может быть использован для запросов, которые задают только значение last_name, так как этот столбец является левым префиксом индекса (как описано позже в этом разделе). Поэтому, индекс name используется для поиска в следующих запросах:

SELECT * FROM test WHERE last_name='Jones';

SELECT * FROM test
  WHERE last_name='Jones' AND first_name='John';

SELECT * FROM test
  WHERE last_name='Jones'
  AND (first_name='John' OR first_name='Jon');

SELECT * FROM test
  WHERE last_name='Jones'
  AND first_name >='M' AND first_name < 'N';

Однако, индекс name не используется для поиска в следующих запросах:

SELECT * FROM test WHERE first_name='John';

SELECT * FROM test
  WHERE last_name='Jones' OR first_name='John';

Предположим, что вы выполняете следующую SELECT команду:

SELECT * FROM tbl_name
  WHERE col1=val1 AND col2=val2;

Если существует индекс по нескольким столбцам на col1 и col2, соответствующие строки могут быть получены непосредственно. Если существуют отдельные индексы по одному столбцу на col1 и col2, оптимизатор пытается использовать оптимизацию слияния индексов (см. Раздел 8.2.1.3, «Оптимизация слияния индексов»), или пытается найти наиболее ограничительный индекс, определив, какой индекс исключает больше строк, и использовать этот индекс для извлечения строк.

Если таблица имеет индекс по нескольким столбцам, любой левый префикс индекса может быть использован оптимизатором для поиска строк. Например, если у вас есть индекс по трём столбцам на (col1, col2, col3), у вас есть возможности поиска по (col1), (col1, col2) и (col1, col2, col3).

MySQL не может использовать индекс для поиска, если столбцы не образуют левого префикса индекса. Предположим, что у вас есть SELECT команды, показанные здесь:

SELECT * FROM tbl_name WHERE col1=val1;
SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2;

SELECT * FROM tbl_name WHERE col2=val2;
SELECT * FROM tbl_name WHERE col2=val2 AND col3=val3;

Если существует индекс на (col1, col2, col3), только первые два запроса используют индекс. Третий и четвёртый запросы включают индексированные столбцы, но не используют индекс для поиска, поскольку (col2) и (col2, col3) не являются левыми префиксами индекса (col1, col2, col3).