MySQL is not using keys on the outer query of a subselect with "IN"

Question

Hi,

i'm querying a database like this:

 SELECT * from Log WHERE cookieId IN (select cookieId from Log WHERE someId="blafasel");

I have keys on someId and cookieId but yet the query is very slow. If I run the two queries (the outer and the inner) separated both of them or very fast.

 select cookieId from Log WHERE someId="blafasel"

gets me the results almost instant. And so does a query

 SELECT * FROM Log WHERE cookieId IN ("something","somethingelse","athirdoption")

Using EXPLAIN tells me that keys are used in the two single queries but for the subselect query keys are only used for the inner select. My Question is why? And how to tell MySQL to be a little bit more clever.

Well I could let my application run the two queries separated but that wouldnt be convenient.

Thanks for your help.

Answer 1

Have you tried using

SELECT  l.*
FROM    log l INNER JOIN
        log ls ON l.cookieid = ls.cookieid
WHERE   ls.someId="blafasel"

Answer 2

Have you tried simply this?

SELECT * from Log WHERE someId="blafasel";

I imagine your real query is a bit different, but just in case it's not.

Also, does explain show the correct key in the "possible keys" column? If so, you could try using a "force index(index_name)" clause on the outer select to see if that speeds it up.

Answer 3

Further (interesting) references:

http://bugs.mysql.com/bug.php?id=9090 http://dev.mysql.com/doc/refman/4.1/en/subquery-restrictions.html