When to create multiple-column index in Mysql?
Should I measure the slow queries and create multiple-column index for them?
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 only takes a minute to sign up.Sign up to join this community
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
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..
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.