Hello Friends
I am facing major speed problem in mysql database. I am joining some tables to fetch data. In query I have to use one OR condition and this condition is causing problem. If I remove this condition then speed is acceptable. Below is query

SELECT a.laname, a.cfcc, b.cenid, b.polyid, c.tlid, d.fename, d.fetype, d.frlong as d_frlong, d.frlat as d_frlat, d.tolong as d_tolong, d.tolat as d_tolat, p.polylong, p.polylat, p.water ,e.rtsq, e.long1, e.lat1, e.long2, e.lat2, e.long3, e.lat3, e.long4, e.lat4, e.long5, e.lat5, e.long6, e.lat6, e.long7, e.lat7, e.long8, e.lat8, e.long9, e.lat9, e.long10, e.lat10 FROM (((( ( rt7 as a inner join rt8 as b on a.land = b.land ) inner join rti c on ( (b.cenid = c.cenidr and b.polyid = c.polyidr)or(b.cenid = c.cenidl and b.polyid = c.polyidl)) ) inner join rt11 as d on c.tlid = d.tlid) inner join rtp as p on p.cenid = b.cenid and p.polyid = b.polyid )) left join rt2 as e on d.tlid = e.tlid WHERE a.laname like 'Mill Pond%' and ( ( 3958.682 ) * ( acos( sin( ( d.frlat ) * 0.01745 ) * sin( ( 39.491506 ) * 0.01745 ) + cos( ( d.frlat ) * 0.01745 ) * cos( ( 39.491506 ) * 0.01745 ) * cos( ( d.frlong - ( - 74.504574 ) ) * 0.01745 ) ) ) ) <= 5 ORDER BY c.tlid, rtsq


In this query following condition is causing problem

( (b.cenid = c.cenidr and b.polyid = c.polyidr)or(b.cenid = c.cenidl and b.polyid = c.polyidl))

If I use any of above condition at a time then ourput is fast.
1.(b.cenid = c.cenidr and b.polyid = c.polyidr)
2.(b.cenid = c.cenidl and b.polyid = c.polyidl)

Indexing for table c is done on all four fields
c.cenidr
c.polyidr
c.cenidl
c.polyidl

Please suggest where I am making mistake and what should I do to speed up output. Same problem is in another query also. There also OR condition is causing problem.

Thanks

Hello,

I'm not sure of the result, but in other circumstances, I encountered a very slow query with an 'OR' condition, I rewrote it as a union and it got much faster (but I'm not sure it is always the case).

So, instead of :

SELECT ... with condition (A OR B) ... ORDER BY ...

You can try :

SELECT * from
(
(SELECT ... with condition A)
UNION
(SELECT ... with condition B)
)
ORDER BY ...

I think you should also create double-field indexes on c(cenidr,polyidr) and c(cenidl,polyidl). That should help.

Regards,

RBARAER