Mysql speed & indexing
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