CODEWITHSUNDEEP
mysqlsqlindexingquery-performance
Raymond.Hsu
Raymond.Hsu

Reputation: 103

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

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  |
+----+-------------+---------+-------+---------------+---------+---------+-------------+------+-------+

Upvotes: 9

Views: 5703

Answers (2)

O. Jones
O. Jones

Reputation: 108641

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.

Upvotes: 6

axiac
axiac

Reputation: 72177

The order of columns in a multi-column index matters.

The documentation of the multiple-column indexes reads:

MySQL can use multiple-column indexes for queries that test all the columns in the index, or queries that test just the first column, the first two columns, the first three columns, and so on. If you specify the columns in the right order in the index definition, a single composite index can speed up several kinds of queries on the same table.

This means an index on columns name and city can be used when an index on column name is needed but it cannot be used instead of an index on column city.

The order of conditions in the WHERE clause doesn't matter. The MySQL optimizer does a lot of work on the conditions on the WHERE clause to eliminate as many candidate rows as possible as early as possible and to read as little data as possible from the tables and indexes (because some of the read data is dropped because it doesn't match the entire WHERE clause).

Upvotes: 4

Related Questions