Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

When to create multiple-column index in Mysql?

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

share|improve this question
add comment

2 Answers

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

share|improve this answer
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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