MySQL Index Question

Order of Multi-Column Indexes

[Mr_Fern]

I understand that the rightmost part of a multicolumn index can not be used to optimize a query unless all of the parts of the index have been used.

However I have a question regarding the order in which their used in particular queries.

For example:

Query 1: SELECT * FROM tableA WHERE key_part1 = x AND key_part2 = y AND key_part3 = z ORDER BY key_part4 LIMIT 5

Query 2: SELECT * FROM tableA WHERE key_part2 = y AND key_part3 = z AND
key_part1 = x ORDER BY key_part4 LIMIT 5

Would these two queries be optimized the same way?

And if there was a query 3

Query 3: SELECT * FROM tableA WHERE key_part1 = x AND key_part2 = y AND key_part4 = z ORDER BY key_part3 LIMT 5

Would that use make use of the key for all 4 parts, or just parts 1,2, and 3?

[Mr_Fern]

I ended up deciding to try on another database somewhere else with a small amount of data and got the answers I wanted. But for the sake of those who might read this and have a similar question, these are the results I found.

Query 1 and Query 2 will be optimized the same way. It doesn't matter the order in which you place the parts of the index in the WHERE, as long as they are present it will work.

Query 3 will utilize parts 1-3, 4 does not get used.