基于慢速位置的搜索结果查询 [英] Slow location based search result query

问题描述

我有一个查询，用于查找按位置排序的结果.结果还必须考虑增值税，因此这也在查询中.不幸的是，该查询在未缓存时可能需要4秒钟以上的时间才能运行.谁能发现任何明显的问题或建议我做些什么来改善它?

I have a query that I use to find results that are ordered by location. Results also have to account for VAT so this is also in the query. The query can unfortunately take 4+ seconds to run when not cached. Can anyone spot any glaringly obvious issues or suggest anything I can do to improve it?

只是为了澄清查询中发生的事情:

Just to clarify what is happening in the query:

• 距离是使用纬度/经度计算的欧几里得距离
• incvat字段用于显示包含增值税的价格
• WHEN/THEN语句用于将价格0置于最底端

查询:

SELECT * , ROUND( SQRT( POW( ( 69.1 * ( company_branch_lat - 52.4862 ) ) , 2 ) + POW( ( 53 * ( company_branch_lng - - 1.8905 ) ) , 2 ) ) , 1 ) AS distance, 
    hire_car_day + ( hire_car_day * 0.2 * ! hire_car_incvat ) AS hire_car_day_incvat, 
    hire_car_addday + ( hire_car_addday * 0.2 * ! hire_car_incvat ) AS hire_car_addday_incvat, 
    hire_car_week + ( hire_car_week * 0.2 * ! hire_car_incvat ) AS hire_car_week_incvat, 
    hire_car_weekend + ( hire_car_weekend * 0.2 * ! hire_car_incvat ) AS hire_car_weekend_incvat
FROM hire_car
LEFT JOIN company_branch ON company_branch_id = hire_car_branchid
LEFT JOIN hire_cartypelink ON hire_cartypelink_carhireid = hire_car_id
LEFT JOIN users ON company_branch_userid = user_id
WHERE 1 
GROUP BY hire_car_id
HAVING distance <=30
ORDER BY CASE hire_car_day_incvat
WHEN 0 
THEN 40000 
ELSE hire_car_day_incvat
END , distance ASC 
LIMIT 0 , 30

推荐答案

您可以使用mysql空间扩展并将纬度和经度保存为点数据类型，并使其成为空间索引.这样，您可以沿曲线对坐标进行重新排序并减小尺寸并保留空间信息.您可以使用空间索引作为边界框来过滤查询，然后使用harvesine公式选择最佳结果.您的边界框应大于大圆的半径. Mysql使用带有一些空间索引的rtree，而我的示例是关于z曲线或希尔伯特曲线的: http://dev.mysql.com/doc/refman/5.0/en/creating-spatial-values.html .或者，您可以使用几何数据类型: http://markmaunder .com/2009/10/10/mysql-gis-extensions-quick-start/.然后，您可以使用MBRcontains函数，如下所示: http ://dev.mysql.com/doc/refman/4.1/en/relations-on-geometry-mbr.html 或其他任何功能:

You can use the mysql spatial extension and save the latitude and longitude as a point datatype and make it a spatial index. That way you can reorder the coordinates along a curve and reduce the dimension and preserve spatial information. You can use the spatial index as a bounding box to filter the query and then use the harvesine formula to pick the optimal result. Your bounding box should be bigger then the radius of the great circle. Mysql uses a rtree with some spatial index and my example was about a z curve or a hilbert curve: https://softwareengineering.stackexchange.com/questions/113256/what-is-the-difference-between-btree-and-rtree-indexing. Then you can insert a geocoordinate directly into a point column: http://dev.mysql.com/doc/refman/5.0/en/creating-spatial-values.html. Or you can use a geometry datatype: http://markmaunder.com/2009/10/10/mysql-gis-extensions-quick-start/. Then you can use MBRcontains function like so: http://dev.mysql.com/doc/refman/4.1/en/relations-on-geometry-mbr.html or any other functions: http://dev.mysql.com/doc/refman/5.5/en/functions-for-testing-spatial-relations-between-geometric-objects.html. Hence you need a bounding box. Here are some examples:

• 使用Spatial Point在MySQL中存储Lat Lng值输入
• > https://gis.stackexchange.com/questions/28333/how-to-speed-up-up-up-this-simple-mysql-points-in-the-box-query

• Storing Lat Lng values in MySQL using Spatial Point Type
• https://gis.stackexchange.com/questions/28333/how-to-speed-up-this-simple-mysql-points-in-the-box-query

这是一个简单的具有点数据类型的示例:

Here is a simple example with point datatype:

    CREATE SPATIAL INDEX sx_place_location ON place (location)

    SELECT  * FROM    mytable
    WHERE   MBRContains
           (
           LineString
                   (
                   Point($x - $radius, $y - $radius),
                   Point($x + $radius, $y + $radius)
                   )
           location
           )
    AND Distance(Point($x, $y), location) <= $radius

• MySQL纬度和经度表设置.
• MySQL latitude and Longitude table setup.

我不确定它是否有效，因为它使用了带有定界框功能的radius变量.在我看来，MBRwithin有点简单，因为它不需要任何参数:

I'm not sure if it works because it's uses a radius variable with a bounding-box function. It's seems to me MBRwithin is a bit simpler, because it doesn't need any argument: Mysql: Optimizing finding super node in nested set tree.

这篇关于基于慢速位置的搜索结果查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！