sql - MySql takes a long time optimizing a join-less query -


we have simple query looks like:

select a,b,c,d table a=1 , b in ('aaa', 'bbb', 'ccc', ...) 

no joins @ all, 5000 contsant values in in clause.

now, query takes 1-20 seconds run on strong (16 core) server. table has index on (a,b), , tried reversing index (b,a). server has tons of memory, , nobody writing table - 5 processes running selects described above.

we did profiling , saw queries spend 3.5 seconds in "join::optimize" (.\sql_select.cc 977). remind you, queries not use joins @ all.

what cause large time spent optimizing joins on join-less table?

here result of explain select:

id select_type table type   possible_keys key    key_len ref rows   1  simple     table range    ix_a_b       ix_a_b 65      \n  5000   using 

try putting 5000 values in temporary table:

declare @t table (b varchar(10)) insert b select 'aaa' union select 'bbb' union select 'c' ....  select table.* table join @t t on table.b = t.b table.a = 1 

Comments

Popular posts from this blog

c++ - Convert big endian to little endian when reading from a binary file -

C#: Application without a window or taskbar item (background app) that can still use Console.WriteLine() -

unicode - Are email addresses allowed to contain non-alphanumeric characters? -