Mysql Join on three tables containing spatial data

Question

I have three tables

Outlets (shopId, shopName,shopLatitude,shopLongitude,g) (450 Rows)

g is of type geometry and contains value point(latitude longitude)

Offers (offerId,offername,offerDescription) (450 Rows)

Offer_Outlets

(offerId,shopId) (503 Rows)

I want to fetch all offers and its shop details within a particular radius, I am following this tutorial for implementing spatial queries.

here is the query i was using which was working fine for few 100's of records, But now with above mentioned number of items in each table its taking about 34s to return results. How can I efficiently write this query?

select DISTINCT     
       ofr.offerId,ofr_otl.shopid,ofr.isdeleted,ofr.offer_title,ofr.offer_icon,
       ofr.offer_description,ofr.CategoryId,ofr.offer_terms,
       ofr.start_date,ofr.end_date,
       ofr.price_description,
       otl.shop_name,otl.shop_address,otl.shop_city,
       otl.shop_phone,otl.shop_icon,
       otl.shop_latitude,otl.shop_longitude,otl.shop_country,otl.shop_zip,
       get_distance(x(g),y(g),8.4901831,76.9558434) as distance,
       otl.shop_weblink,
       ofr.off_Angle,ofr.rating
  from offers as ofr,outlets as otl,off_outlets as ofr_otl 
 where ofr.offerId = ofr_otl.offid 
   and otl.shop_id = ofr_otl.shopid
   and st_within(g,envelope(linestring(
                   point(8.039914120289854, 76.5005853263206), 
                   point(8.940452079710145, 77.41110147367941))))
   and ofr.isdeleted = 0
 order by offer_title
 LIMIT 300 ;

I am afraid most of the time it is calculating st_within() function, which is, I believe, user defined function for whether something is within a circle. Try first filter rows within a square, and only these, that pass, filter through circle. Also, store latitude and longitude as separate numeric values. To de-envelope and convert them all every query may be noticeable. — David162795, Nov 08 '14 at 22:26
Questions: What version of MySQL? What access method (MyISAM? InnoDB?) for the tables? What indexes do you have? What is this `get_distance` function you're using? Observation: It seems likely you're scanning every row in your joined set of tables to evaluate `st_within()` — O. Jones, Nov 08 '14 at 23:01

score 0 · Answer 1 · answered Nov 09 '14 at 02:37

Try this:

SELECT ... FROM 
(
  SELECT * FROM
  (
    SELECT * FROM outlets a
    WHERE a.shopLatitude IS BETWEEN ( ... ) AND a.shopLongitude IS BETWEEN ( ... )
  ) t1
 WHERE st_within(t1.g, ... )
) otl
LEFT JOIN off_outlets as oo ON otl.shop_id = oo.shopid
LEFT JOIN offers as ofr ON ofr.offerId = oo.offid 
WHERE ofr.isdeleted = 0
ORDER BY offer_title
LIMIT 300 ;

It basically forces mysql to first filter shops out within latitude/longitude rectangle, then on st_within, then does the rest.

Mysql Join on three tables containing spatial data

1 Answers1