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:
- If there is a multi-column indexes (name, city), do the two SQLs all use it?
- 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 |
+----+-------------+---------+-------+---------------+---------+---------+-------------+------+-------+