About MySQL Many Column Indexes

While indexes can contain multiple columns, there is a practical limit in the effectiveness of the index. Indexes are part of the relational model to improve performance. The index row width should be as short as practical in order to provide as many index records per index data page. The benefit is to traverse the index as quickly as possible by reading the least amount of data. You also want to keep your indexes efficient so you can maximize the use of your system memory. The EXPLAIN command provides the key_len and ref attributes to determine the column utilization of selected indexes. Here is an example:

mysql> ALTER TABLE artist ADD index (type,gender,country_id);
mysql> EXPLAIN SELECT name FROM artist WHERE type= 'Person' AND
gender='Male' AND country_id = 13\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: artist
         type: ref
possible_keys: type,country_id,type_2
          key: type_2
      key_len: 6
          ref: const,const,const
         rows: 40
        Extra: Using where

As you can see the ref columns shows three constants, which match three columns of the index. The key_len of 6 can also confirm this: 1 byte for ENUM, 2 bytes for SMALLINT, 1 byte for nullability, 1 byte for ENUM, 1 byte for nullability.

If we did not restrict our query by country we would see the following:

mysql> EXPLAIN SELECT name FROM artist WHERE type= 'Person' AND
gender='Male'\G
*************************** 1. row ***************************
...
           key: type_2
      key_len: 3
          ref: const,const
...

This highlights that while the index was used, the additional column is not used for this query. If no other queries utilize this third column, this is an optimization to reduce the index row width.