CODEWITHSUNDEEP
sqlmysqlpostgresqlindexing
medihack
medihack

Reputation: 16627

Order of multi column index and SQL query

I found two contrary statements regarding the order of multi column indexes in MySQL. This post here has in the comments that an (a, b) index would also be used for a query with (b = value1 AND a = value2). This FAQ entry here says (on the bottom) exactly the opposite (index won't be used). What is correct? And how about PostgreSQL? Does it behave the same way?

Upvotes: 1

Views: 698

Answers (1)

RichardTheKiwi
RichardTheKiwi

Reputation: 107716

First let me say, there is no silver bullet answer.

And index on (a,b) composite index will be used to satisfy a query (b=value1 and a=value2). Note that it does not matter that "b" is presented before "a" in the WHERE clause, since the query engine knows you are dealing with both a and b. It STILL may not be used if the selectivity is not high enough.

Having said that

2. [SELECT * FROM buyers WHERE last_name=? AND first_name=? AND zip=?]
can't use the index.

That has to top my unbelievable-faq-entry find for today. It is partially right, and only because the SELECT * requires a lookup back to the table so any benefit from the composite index is halved (or further minimized). Compare the two queries at the end of this code instead

CREATE TABLE buyers(
 buyer_id INT NOT NULL AUTO_INCREMENT,
 first_name CHAR(19) NOT NULL,
 last_name CHAR(19) NOT NULL,
 zip CHAR(5) NOT NULL,
 state_code CHAR(2) NOT NULL,
 PRIMARY KEY (buyer_id)
 );

insert buyers values
(991,'zeshan ','Nadeem ',92082,'CA'),
(992,'Ken ','Marcus ',92082,'CA'),
(993,'Tariq ','Iqbal ',92082,'CA'),
(994,'Tariq ','Iqbal ',92082,'CA'),
(995,'Hasnat ','Ahmad ',92083,'NY'),
(996,'Tariq ','Iqbal ',92082,'DC'),
(997,'Keith ','Worlf ',93083,'NG'),
(998,'Ashley ','Lewis ',92088,'NJ'),
(999,'Tariq ','Mehmood ',99088,'TX');

ALTER TABLE buyers ADD INDEX idx_firstname (first_name);
ALTER TABLE buyers ADD INDEX idx_last_name (last_name);
ALTER TABLE buyers ADD INDEX idx_zip (zip);
ALTER TABLE buyers ADD INDEX idx_flname_zip(first_name,last_name,zip);

Run this in a separate query

explain
SELECT first_name,last_name,zip FROM buyers WHERE first_name='Tariq' AND last_name='Iqbal' AND zip=92082;

And then this

explain
SELECT last_name,first_name,zip FROM buyers WHERE last_name='Iqbal' AND first_name='Tariq' AND zip=92082;

They will show the same plan

Upvotes: 2

Related Questions