MySql takes a long time optimizing a join-less query

Question

We have a simple query that looks like:

SELECT a,b,c,d FROM table WHERE a=1 and b IN ('aaa', 'bbb', 'ccc', ...)

No joins at all, 5000 contsant values in the IN clause.

Now, this query takes 1-20 seconds to run on a very strong (16 core) server. The table has an index on (a,b), and we also tried reversing the index to (b,a). The server has tons of memory, and nobody is writing to this table - just 5 processes running selects like I described above.

We did some profiling and saw that some queries spend 3.5 seconds in "JOIN::optimize" (.\sql_select.cc 977). I remind you, the queries do not use joins at all.

What could be the cause for this large time spent optimizing joins on a join-less table?

Here is the result of EXPLAIN SELECT:

id select_type table type   possible_keys key    key_len ref rows   Extra
1  SIMPLE     table range    IX_A_B       IX_A_B 65      \N  5000   Using where

Answer 1

Do you have indexes on field a and especially b?

If you are asking for help in optimization SQL you should attach

EXPLAIN SELECT a,b,c,d FROM table WHERE a=1 and b IN ('aaa', 'bbb', 'ccc', ...)

as well, without it people can only guess.

Answer 2

Try putting 5000 values in a temporary table:

declare @t table (b varchar(10))
insert into b select 'aaa'
union all select 'bbb'
union all select 'c'
....

select table.*
from table
join @t t on table.b = t.b
where table.a = 1

Answer 3

b IN(x,y,...) gets translated into: (b = x OR b = y OR b = ...)

this means you have 5000 if-checks to do for each value in the table.

Answer 4

Using an IN clause like that may as well be a join, so it's not completely join-less.

It's fairly good that you have an index on (a,b), but you have to wonder how it's going to get at the values c and d... in the end, it'll probably be ignoring the index and just scanning the whole table.

Try making an index on (a,b,c,d), so that the index contains all the data you need. In SQL Server you'd do this with included columns, but I think in mysql you'd need to just include the others too. This should mean that your query can go straight to the a=1 records, and start looking through for records of b that match the list, and then it has all the information it needs.

Answer 5

Our DBA found this as a reported bug.

Answer 6

Your answer will be to consider the suggestions in both of the following answers:
http://stackoverflow.com/questions/2153799/mysql-takes-a-long-time-optimizing-a-join-less-query/2153819#2153819
http://stackoverflow.com/questions/2153799/mysql-takes-a-long-time-optimizing-a-join-less-query/2153860#2153860

In addition, you mentioned that b is highly selective; so:

I suggest you change the order of the columns in your index to (b, a). If the optimser can narrow down on your results more quickly, it will be keener to use the index. (It's generally a good rule of thumb to put your most selective columns earlier in your indexes; rarely if ever would you want to deviate from that principle.)