snippetsqlMinor

When to create multiple-column index?

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

createcolumnmultiplewhenindex

Problem

When to create multiple-column index in Mysql?

Should I measure the slow queries and create multiple-column index for them?

Solution

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 2 columns, the first 3 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.

SELECT * FROM test WHERE first_name='Andrew';

SELECT * FROM test
  WHERE last_name='Smith' OR first_name='Andrew';

Suppose that you issue the following SELECT statement:

mysql> SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2;

If a multiple-column index exists on col1 and col2, the appropriate rows can be fetched directly. If separate single-column indexes exist on col1 and col2, the optimizer attempts to use the Index Merge optimization..

Source: http://dev.mysql.com/doc/refman/5.0/en/multiple-column-indexes.html

Code Snippets

SELECT * FROM test WHERE first_name='Andrew';

SELECT * FROM test
  WHERE last_name='Smith' OR first_name='Andrew';

mysql> SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2;

Context

StackExchange Database Administrators Q#12618, answer score: 7

Revisions (0)

No revisions yet.