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
Post a Comment