MySQL Query Slowed by Parameter Check on Joined Table -

i writing stored procedure function take number of parameters (populated end user) , perform search. however, parameter has no value, should not included in clause. carrying out check on parameter first part of or clause, so:

... (param1 = '' or field1 = param1) , (param2 = '' or field2 = param2)  ... 

i have incorporated general select statement so:

select * table1 t1 (param1 = '' or t1.field1 = param1) 

please note have included 1 parameter in example clarity. real query contains multiple parameters , field comparisons.

this works fine , runs (e.g. 'real' query returns result in approx 0.5 secs).

so far good. however, when introduce joined table, things start going wrong. example if execute query below, takes far longer (3-4 seconds on real query):

select * table1 t1  left join table2 t2 on t1.table2id = t2.id  (param1 = '' or t2.field1 = param1) 

please note parameter being compared field in joined table (table2).

please note in both of these examples, when running query have ensured param1 populated value.

now, if remove param1 = '' or section of above clause, runs previous un-joined query.

none of fields used in tables indexed although have tried , made no difference problem have.

so, explain me why first query (single table) works second query (joined tables) takes disproportionately longer?

many in advance.

you try making view, , use result view in join statement.

create view `vw_my_simple_table_view_name` select * table1 t1 (param1 = '' or t1.field1 = param1) 

and query this(might need adaptation)

select * vw_my_simple_table_view_name t1  left join table2 t2 on t1.table2id = t2.id  (t2.field1 = param1)