Mysql连接包含空间数据的三个表

Question

我有三个表

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)

我想获取特定半径内的所有报价及其商店详细信息，我正在关注此 tutorial用于实现空间查询。

这是我正在使用的查询，它可以很好地处理几百条记录，但是现在每个表中都有上述数量的项目，它需要大约 34 秒才能返回结果。我怎样才能有效地编写这个查询？

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 ;

Answer 1

试试这个:

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 ;

它基本上强制 mysql 首先在纬度/经度矩形内过滤掉商店，然后在 st_within 上，然后做剩下的事情。