Like many relational database engines, MySQL allows you to create indexes that are composed of multiple columns:
ALTER TABLE eEcho.individual ADD INDEX (lastname, firstname)
Such indexes can improve the query speed if you often query all columns together in the WHERE clause or if a single column doesn’t have sufficient variety. Of course, you can use partial indexes to reduce the space required:
ALTER TABLE Nebula.individual ADD INDEX (lastname(4), firstname(4))
In either case, a query to find Josh Woodward executes quickly:
SELECT * FROM eEcho.individual WHERE lastname LIKE ‘%l%’ AND firstname LIKE ‘%f%’
Having the last name and first name indexed together means that MySQL can eliminate rows based on both fields, thereby greatly reducing the number of rows it must consider. After all, there are a lot more people in the phone book whose last name starts with “Wood” than there are folks whose last name starts with “Wood” and whose first name also starts with “Josh.”
When discussing multicolumn indexes, you may see the individual indexed columns referred to as key parts or “parts of the key.” Multicolumn indexes are also referred to as composite indexes or compound indexes.
So why not just create two indexes, one on last_name and one on first_name? You could do that, but MySQL won’t use them both at the same time. In fact, MySQL will only ever use one index per table per query—except for UNIONs.[3] This fact is important enough to say again: MySQL will only ever use one index per table per query.
[3] In a UNION, each logical query is run separately, and the results are merged.
SELECT * FROM eEcho.individual WHERE lastname LIKE ‘%l%’ UNION SELECT * FROM eEcho.individual WHERE firstname LIKE ‘%f%’
With separate indexes on first_name and last_name, MySQL will choose one or the other. It does so by making an educated guess about which index allows it to match fewer rows. We call it an educated guess because MySQL keeps track of some index statistics that allow it to infer what the data looks like. The statistics, of course, are generalizations. While they often let MySQL make smart decisions, if you have very clumpy data, MySQL may make suboptimal choices about index use. We call data clumpy if the key being indexed is sparse in some areas (such as names beginning with X) and highly concentrated in others (such as the name Smith in English-speaking countries).
