Juris Juris - 1 month ago 5
MySQL Question

Will MySQL use Multiple-column index if I use columns in different order?

Reading the MySQL docs we see this example table with multiple-column index

name
:

CREATE TABLE test (
id INT NOT NULL,
last_name CHAR(30) NOT NULL,
first_name CHAR(30) NOT NULL,
PRIMARY KEY (id),
INDEX name (last_name,first_name)
);


It is explained with examples in which cases the index will or will not be utilized. For example, it will be used for such query:

SELECT * FROM test
WHERE last_name='Widenius' AND first_name='Michael';


My question is, would it work for this query (which is effectively the same):

SELECT * FROM test
WHERE first_name='Michael' AND last_name='Widenius';


I couldn't find any word about that in the documentation - does MySQL try to swap columns to find appropriate index or is it all up to the query?

Answer

Should be the same because (from mysql doc) the query optiminzer work looking at

Each table index is queried, and the best index is used unless the optimizer believes that it is more efficient to use a table scan. At one time, a scan was used based on whether the best index spanned more than 30% of the table, but a fixed percentage no longer determines the choice between using an index or a scan. The optimizer now is more complex and bases its estimate on additional factors such as table size, number of rows, and I/O block size.

http://dev.mysql.com/doc/refman/5.7/en/where-optimizations.html

In some cases, MySQL can read rows from the index without even consulting the data file.

and this should be you case

Without ICP, the storage engine traverses the index to locate rows in the base table and returns them to the MySQL server which evaluates the WHERE condition for the rows. With ICP enabled, and if parts of the WHERE condition can be evaluated by using only fields from the index, the MySQL server pushes this part of the WHERE condition down to the storage engine. The storage engine then evaluates the pushed index condition by using the index entry and only if this is satisfied is the row read from the table. ICP can reduce the number of times the storage engine must access the base table and the number of times the MySQL server must access the storage engine.

http://dev.mysql.com/doc/refman/5.7/en/index-condition-pushdown-optimization.html