Is sql use multiple column indexes?(MySQL)

Question

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)
);

1. SELECT * FROM test WHERE last_name='bob' and first_name='John';

2. SELECT * FROM test WHERE first_name='John' and last_name='bob';

The first sql must use index, however is the second use the index? and why?

It's a mistake to think where conditions are always evaluated left to right.. — P.Salmon
– P.Salmon, Commented Jul 14, 2020 at 12:20
A side issue: Use VARCHAR, not CHAR (unless all names are expected to be exactly 30 chars long). — Rick James
– Rick James, Commented Jul 14, 2020 at 17:10

sticky bit · Accepted Answer · 2020-07-14 12:17:06Z

1

Both can use the index, if the optimizer "feels" like it. It might, for whatever reason chose not to, but the order of the AND operands shouldn't be such a reason -- in fact I'd considered that a bug if it was.

Since AND commutes, there's no difference if the condition on first_name comes first or second in the AND operation (and analog for last_name).

You can try it yourself. Just compare the plans. Most likely they are identical.

And remember, SQL is a descriptive language. You don't tell the system what to do, but what you want. It'll figure out itself how it gets that and how this is done in a fast way. So it's free to rework the queries and will do so especially with such trivialities as changing the order of operands when possible.

answered Jul 14, 2020 at 12:17

sticky bit

37.7k12 gold badges34 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Rick James Over a year ago

However, that index would be useless if the condition only tested for first_name: WHERE first_name = 'sticky' without also testing last_name. That is, the order in the WHERE does not matter, but the order in the INDEX does. More: mysql.rjweb.org/doc.php/index_cookbook_mysql

Collectives™ on Stack Overflow

Is sql use multiple column indexes?(MySQL)

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related