Does order of columns of Multi-Column Indexes in where clause in MySQL matter?

Question

I have a table below:

CREATE TABLE `student` ( 
     `name` varchar(30) NOT NULL DEFAULT '',
     `city` varchar(30) NOT NULL DEFAULT '', 
     `age`  int(11) NOT NULL DEFAULT '0',
     PRIMARY KEY (`name`,`city`) 
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

I want to know, if I execute the following two SQLs, do they have the same performance?

mysql> select * from student where name='John' and city='NewYork';
mysql> select * from student where city='NewYork' and name='John';

Involved question:

1. If there is a multi-column indexes (name, city), do the two SQLs all use it?
2. Does the optimizer change the second sql to the first because of the index?

I execute explain on the two of them, the result is below:

mysql> explain select * from student where name='John' and city='NewYork';
+----+-------------+---------+-------+---------------+---------+---------+-------------+------+-------+

| id | select_type | table   | type  | possible_keys | key     | key_len | ref         | rows | Extra |
+----+-------------+---------+-------+---------------+---------+---------+-------------+------+-------+

|  1 | SIMPLE      | student | const | PRIMARY       | PRIMARY | 184     | const,const |    1 | NULL  |
+----+-------------+---------+-------+---------------+---------+---------+-------------+------+-------+

mysql> explain select * from student where city='NewYork' and name='John';

+----+-------------+---------+-------+---------------+---------+---------+-------------+------+-------+
| id | select_type | table   | type  | possible_keys | key     | key_len | ref         | rows | Extra |
+----+-------------+---------+-------+---------------+---------+---------+-------------+------+-------+

|  1 | SIMPLE      | student | const | PRIMARY       | PRIMARY | 184     | const,const |    1 | NULL  |
+----+-------------+---------+-------+---------------+---------+---------+-------------+------+-------+

Answer 1

If, given an index on (name,city), I execute the following two SQLs, do they have the same performance?

where name='John' and city='NewYork'

where city='NewYork' and name='John'

Yes.

The query planner doesn't care about the order of WHERE clauses. If both your clauses filter on equality, the planner can use the index. SQL is a declarative language, not procedural. That is, you say what you want, not how to get it. It's a little counterintuitive for many programmers.

It can also use the (name,city) index for WHERE name LIKE 'Raymo%' because name is first in the index. It cannot use that index for WHERE city = 'Kalamazoo', though.

It can use the index for WHERE city LIKE 'Kalam%' AND name = 'Raymond'. In that case it uses the index to find the name value, then scans for matching cities.

If you had an index on (city,name) you could also use that for WHERE city = 'Kalamazoo' AND name = 'Raymond'. If both indexes exist, the query planner will pick one, probably based on some kind of cardinality consideration.

Note. If instead you have two different indexes on city and name, the query planner can't (as of mid-2017) use more than one of them to satisfy WHERE city = 'Kalamazoo' AND name = 'Raymond'.

http://use-the-index-luke.com/ for good info.