Mysql not leveraging primary key in "select * from table where (col,col) in"

I'm trying to run this query:

select * from matrix where (id1, id2) in ((1,2), (3,5));

But its extremely slow. (id1,id2) is a primary key开发者_如何转开发 so this should be a fast look up. Is query planner failing?

Explain says:

|  1 | SIMPLE      | s1_movie | ALL  | NULL          | NULL | NULL    | NULL | 48768876 | Using where |

where 48768876 is the number of rows its scanning.

If I only search for 1 item in the 'in' though, I get rows=1 in the explain, which is correct because it should just be a lookup.

Here's the table:

CREATE TABLE s1_movie (
   id1 INT NOT NULL,
   id2 INT NOT NULL,
   freq INT NOT NULL,
   diff FLOAT NOT NULL,
   PRIMARY KEY (id1, id2)
) ENGINE=INNODB;

How would I go about making this fast? It seems like query planner is failing.

Thanks,

Jon

You could split the WHERE into two single-lookup parts ORed together, but that will become unmanageable if you don't always have two options.

Samples for discussion

CREATE TABLE s1_movie (
   id1 INT NOT NULL,
   id2 INT NOT NULL,
   freq INT NOT NULL,
   diff FLOAT NOT NULL,
   PRIMARY KEY (id1, id2)
) ENGINE=INNODB;

insert into s1_movie select 1,2,3,4;
insert into s1_movie select 1,3,3,4;
insert into s1_movie select 1,4,3,4;
insert into s1_movie select 1,5,3,4;
insert into s1_movie select 1,6,3,4;
insert into s1_movie select 1,7,3,4;
insert into s1_movie select 1,8,3,4;
insert into s1_movie select 1,9,3,4;
insert into s1_movie select 1,0,3,4;
insert into s1_movie select 2,2,3,4;
insert into s1_movie select 2,3,3,4;

Compare

explain extended
select * from s1_movie where (id1, id2) = (1,2) or (id1,id2) = (3,5);

And

explain extended
select * from s1_movie where (id1, id2) in ((1,2), (3,5));

Tuples are pretty aren't they? MySQL handles tuples fine, despite what Mark's answer guesses to be the problem. The issue is the IN clause.

The problem is the way you are doing your IN() statement. "IN" provides an alternative syntax for the "OR" statement. The problem with that is it forces full table scans which is not allowing your indexes to do their job.

Instead, break this down into smaller SELECT statements and use UNION to aggregate your results. This will make your indexes more relevant.

I can't quite tell what you're doing from the way you wrote the example, but something like this:

SELECT * FROM matrix WHERE col1 = var1 AND col2 = var2
UNION
SELECT " FROM matrix WHERE col1 = var3 AND col2 = var4