In the last post, We discussed about the basics of Indexing in database. In this post we will discuss about Composite Indexes and how ordering is important in composite indexes.
We can have indexes on multiple columns which is also called Composite Index. In Mysql, An index may consist of up to 16 columns.
MySQL can use multiple-column indexes for queries that test all the columns in the index, or queries that test just the first column, the first two columns, the first three columns, and so on. If you specify the columns in the right order in the index definition, a single composite index can speed up several kinds of queries on the same table.
Suppose that a table has the following specification:
CREATE TABLE customer (
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)
);
The name index is a composite index over the last_name and first_name columns. The index can be used for lookup in queries that specify values in a known range for combinations of last_name and first_name values. It can also be used for queries that specify just a last_name value because that column is a leftmost prefix of the index (as described later in this section). Therefore, the name index is used for lookup in the following queries:
SELECT * FROM customer WHERE last_name=’Widenius’;
SELECT * FROM customer
WHERE last_name=’Widenius’ AND first_name=’Michael’;
SELECT * FROM customer
WHERE last_name=’Widenius’
AND (first_name=’Michael’ OR first_name=’Monty’);
SELECT * FROM customer
WHERE last_name=’Widenius’
AND first_name >=’M’ AND first_name < ‘N’;
However, the name index is not used for lookup in the following queries:
SELECT * FROM customer WHERE first_name=’Michael’;
SELECT * FROM customer
WHERE last_name=’Widenius’ OR first_name=’Michael’;
If the table has a multiple-column index, any leftmost prefix of the index can be used by the optimizer to look up rows. For example, if you have a three-column index on (column_A, column_B, column_C), you have indexed search capabilities on (column_A), (column_A, column_B), and (column_A, column_B, column_C).
