Will indexes get used if you query a subset of the columns of the index?

Question

So consider I have the following table:

table:

• id (Int, Primary Key)
• c1
• c2
• c3
• c4
• c5

Assume I use queries that differ at runtime (a website for example where checkboxes denote whether or not to search on a specific field), some possible instances:

(Using some x, y, z for the values)

• SELECT * FROM table WHERE c1 = 1 AND c2 = 2
• SELECT * FROM table WHERE c1 = 1 AND c2 = 2 AND c3 = x AND c4 = y
• SELECT * FROM table WHERE c1 = 2 AND c2 = 3
• SELECT * FROM table WHERE c1 = 2 AND c2 = 3 AND c5 = z

The questions:

1. What is the effect of an index on (c1, c2, c3, c4, c5)? How will it be used in the several queries?
2. What would the best indexes be to create on this table?

Answer 1

MySQL documentation does a good job of explaining how multi-column indexes work here.

In general, the index can be used for a where clause when some number of column on the leftmost side of the index have equality conditions. Your where clauses use the following columns:

• c1, c2
• c1, c2, c3, c4
• c1, c2
• c1, c2, c5

Any index starting with columns c1 and c2 would normally be used for these queries. MySQL can apply other conditions to using the index, such as selectivity. That is, if c1 and c2 have constant values (for instance) then using an index will not benefit the query.

To optimize all these combinations, you can create two indexes: c1, c2, c3, c4 and c2, c1, c5. The reason for swapping c1 and c2 in the second index is so you could handle queries where the condition is on c2 but not c1, as well as the reverse.