Composite Indexes: One Index for Multiple Columns
A composite index is like a phone book sorted by last name, then first name. It's for queries filtering on multiple columns, like finding a specific person. The footgun is creating separate indexes, which is far less efficient than a single composite one.
WHY IT EXISTS: Composite indexes exist to dramatically speed up queries that filter on multiple columns at once. A query like WHERE last_name = 'X' AND first_name = 'Y' can be slow if the database has to scan one index and then manually filter the results, or merge results from two separate indexes.
THE MENTAL MODEL: Think of a phone book. It's sorted by last_name, and for every identical last_name, it's then sorted by first_name. This is a composite index on (last_name, first_name). You don't need two separate books (one for last names, one for first names) to find 'Johnson, Andrew'; you use the single, combined-sorted book for one direct lookup.
HOW IT WORKS: A composite index, such as INDEX(last_name, first_name), creates a single B-Tree structure. The data in this tree is sorted first by last_name, and then by first_name for any matching last_name values. When a query filters on both columns, the database can traverse this single B-Tree to find the exact entry. This is much faster than the alternatives. One alternative is using a single index (e.g., on last_name), finding all matching rows, and then filtering them by first_name. Another, called 'Index Merge Intersect,' uses two separate indexes, finds the primary keys from each, and then finds the intersection of those keys before fetching the data. Both are slower than a direct composite index lookup.
WHEN TO USE IT: Use a composite index when your application frequently runs queries with WHERE clauses that filter on the same combination of two or more columns. The order of columns in the index definition is critical and should match the order of columns in your most common queries for best performance.
WHEN NOT TO USE IT: A composite index on (col_A, col_B) is not effective for queries that only filter on col_B, because the index is not sorted primarily by that column. Also, avoid creating composite indexes for column combinations that are rarely queried together, as every index adds write overhead to your database.
ONE CANONICAL EXAMPLE: To find the presidential term for 'Andrew Johnson' from a Presidents table, the query is SELECT term FROM Presidents WHERE last_name = 'Johnson' AND first_name = 'Andrew'. With separate indexes on last_name and first_name, the database might find all 'Johnson's (Andrew and Lyndon B.) and then filter, or merge results from both indexes. With a composite index INDEX(last_name, first_name), it can directly locate the entry for 'Johnson, Andrew' in one operation, retrieve its primary key, and fetch the correct row immediately.
Read the original → mariadb.com
Get five bites like this every day.
Tezvyn delivers a daily feed of 60-second tech bites with quizzes to lock in what you learn.