Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2004
    Posts
    1

    Unanswered: 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

  2. #2
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •