When to create multiple-column index in Mysql?

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


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';

  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

| improve this answer | |

One thing to note, clustered indexes should have a unique key(an identity column I would recommend) as the first column. Basically it helps your data insert at the end of the index and not cause lot's of disk IO and Page splits.

Secondly, if you are created other indexes on your data and they are constructed cleverly they will be reused.

e.g. imagine you search a table on three columns

state, county, zip.

you sometimes search by state only. you sometimes search by state and county. you frequently search by state, county, zip. Then an index with state, county, zip. will be used in all three of these searches.

If you search by zip alone quite a lot then the above index will not be used(by SQL Server anyway) as zip is the third part of that index and the query optimiser will not see that index as helpful.

You could then create an index on Zip alone that would be used in this instance.

I guess the answer you are looking for is that it depends on your where clauses of your frequently used queries and also your group by's.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.