php - How would you optimize the following mysql query -

can me in optimizing mysql query in php, takes 100 sec.

select distinct a.x, a.y, a.z,  tableaa   inner join( tablebb b)   on(a.x = b.x) or (a.x = b.m)  b.n = '$input' or   a.y = '$input' or   a.z = '$input' 

tableaa has 1 million enteries tablebb has 9 million enteries

is there anyother way write query?

edit:

tablebb has primary index on connection between n, x ,y. , indexes on x, n, m

tableaa has primary index on x , indexes on y, z

your query (formatted in way can better understand it) is:

select distinct a.x, a.y, a.z tableaa inner join      tablebb b      on a.x = b.x or a.x = b.m b.n = '$input' or a.y = '$input' or a.z = '$input'; 

this complex optimization because of or clauses. first inclination write 2 joins instead of or:

select distinct a.x, a.y, a.z tableaa left outer join      tablebb b      on a.x = b.x , b.n = '$input' left outer join      tablebb b2      on a.x = b2.m , b2.n = '$input' (b.n not null or b2.n not null) , (a.y = '$input' or a.z = '$input') 

this more complicated, can add indexes on b(n, x) , b(n, m) facilitate processing.

handling or condition on a complicated. can try having 2 indexes: a(y, x, z) , a(z, x, y) , see if used. otherwise, can split 2 queries:

(select a.x, a.y, a.z  tableaa left outer join       tablebb b       on a.x = b.x , b.n = '$input' left outer join       tablebb b2       on a.x = b2.m , b2.n = '$input'  (b.n not null or b2.n not null) , a.y = '$input' ) union (select a.x, a.y, a.z  tableaa left outer join       tablebb b       on a.x = b.x , b.n = '$input' left outer join       tablebb b2       on a.x = b2.m , b2.n = '$input'  (b.n not null or b2.n not null) , a.z = '$input' ) 

this version should use indexes mentioned above. of course, need check explain plans see if case. might optimization.

if there way simplify conditions and rather or, such optimization easier.