CODEWITHSUNDEEP
mysqlindexing
xiaozhiliaoo
xiaozhiliaoo

Reputation: 451

Is sql use multiple column indexes?(MySQL)

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?

Upvotes: 0

Views: 27

Answers (1)

sticky bit
sticky bit

Reputation: 37472

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.

Upvotes: 1

Related Questions