Optimizing your MySQL Application

Single- vs. Multi-column Indexes

You’ve probably noticed that in the CREATE INDEX, ALTER TABLE and CREATE TABLE queries above, I made references to columns (plural). Again, this can be best explained by an example.

Here’s a more complex version of the people table:

CREATE TABLE people (  
 peopleid SMALLINT NOT NULL AUTO_INCREMENT,  
 firstname CHAR(50) NOT NULL,  
 lastname CHAR(50) NOT NULL,  
 age SMALLINT NOT NULL,  
 townid SMALLINT NOT NULL,  
 PRIMARY KEY (peopleid)  
);

(Note that because “peopleid” is an AUTO_INCREMENT field, it must be declared the primary key)

A small snippet of the data we insert may look like this (ignore townid for now):

From this snippet, we have four Mikes (two Sullivans, two McConnells), two 17 year olds, and an unrelated odd ball (Joe Smith).

My intended use for this table is to get the peopleid for users with a specific first name, last name, and age. For example, I want to find the peopleid for Mike Sullivan, aged 17 (SELECT peopleid FROM people WHERE firstname='Mike' AND lastname='Sullivan' AND age=17;). Since I don’t want to have MySQL do a full table scan, I need to look into some indexing.

My first option is to create an index on a single column, firstname, lastname, or age. If I put the index on firstname (ALTER TABLE people ADD INDEX firstname (firstname);), MySQL will use the index to limit the records to those where firstname=’Mike’. Using this “temporary result set,” MySQL will apply each additional condition individually. First it eliminates those whose last name isn’t Sullivan. Then it eliminates those who aren’t 17. MySQL has now applied all conditions and can return the results.

This is more efficient than forcing MySQL to do a full table scan, but we’re still forcing MySQL to scan significantly more rows than it needs to. We could drop the index on firstname and add an index on lastname or age, but the results would be very similar.

Here's where multi-column indexes come into play. If we add a single index on three columns, we can get the correct set in a single pass! Here is the code I use to add this index:

ALTER TABLE people ADD INDEX fname_lname_age (firstname,lastname,age);

Since the index file is organized, MySQL can jump directly to the correct first name, then move to the correct last name, and finally go directly to the correct age. MySQL has found the correct rows without having to scan a single row of the data file!

Now, you’re probably wondering if creating three single-column indexes on (firstname), (lastname), and (age) is the same as one multi-column index on (firstname,lastname,age). No: it's completely different. When running a query, MySQL can only use one index. If you have three single-column indexes, 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 on (firstname,lastname,age).

Leftmost Prefixing

Multi-column indexes provide an additional benefit through what is known as leftmost prefixing. To continue our previous example, we have a three-column index on (firstname,lastname,age), which I have nicknamed “fname_lname_age” (I’ll explain more about that later). This index will be used when searching the following combination of columns:

• firstname,lastname,age
• firstname,lastname
• firstname

To put it another way, we have basically created indexes on (firstname,lastname,age), (firstname,lastname), and just (firstname). The following queries can use the index:

SELECT peopleid FROM people WHERE firstname=’Mike’  
  AND lastname=’Sullivan’ AND age=’17’;  
SELECT peopleid FROM people WHERE firstname=’Mike’  
  AND lastname=’Sullivan’;  
SELECT peopleid FROM people WHERE firstname=’Mike’;

The following queries cannot use the index at all:

SELECT peopleid FROM people WHERE lastname=’Sullivan’;  
SELECT peopleid FROM people WHERE age=’17’;  
SELECT peopleid FROM people WHERE lastname=’Sullivan’  
  AND age=’17’;