我使用了这个查询,mysql花了太长时间才给出结果。行数为160万行。
SELECT DISTINCT TB.ID, Latitude, Longitude,
111151.29341326*SQRT(pow(-6-`Latitude`,2)
+pow(106-`Longitude`,2)*cos(-6*0.017453292519943)
*cos(`Latitude`*0.017453292519943)) as Distance
FROM `tablebusiness` AS TB, `tablecity` AS TC, `businessestag` AS BC,
`businessesdistricts` AS BD, `tabledistrict` AS TD,
(SELECT ID,
(SELECT Title
FROM `tablebusiness` As TBuild
WHERE TBuild.ID = TBB.Building) As BuildingTitle
FROM `tablebusiness` As TBB) AS TBuilding
WHERE TB.City = TC.City AND BC.BusinessID = TB.ID AND BD.BusinessID = TB.ID
AND TD.ID = BD.District AND TBuilding.ID = TB.ID
AND (`Title` LIKE '%%' OR `Street` LIKE '%%' OR TB.City LIKE '%%'
OR Country LIKE '%%' OR Tag LIKE '%%' OR TD.District LIKE '%%'
OR TBuilding.BuildingTitle LIKE '%%')
AND (-6.0917668133836 < `Latitude` AND `Latitude` < -5.9082331866164
AND 105.90823318662 < `Longitude` AND `Longitude` < 106.09176681338)
ORDER BY Distance LIMIT 0, 100即使行数是查询的160万部分,也就是
AND (-6.0917668133836 < `Latitude` AND `Latitude` < -5.9082331866164 AND 105.90823318662 < `Longitude` AND `Longitude` < 106.09176681338) 极大地限制了行数。看起来mysql似乎不会费心通过搜索
AND (-6.0917668133836 < `Latitude` AND `Latitude` < -5.9082331866164 AND 105.90823318662 < `Longitude` AND `Longitude` < 106.09176681338) first.我通过移动前面的纬度、经度方面更改了查询
SELECT DISTINCT TB.ID, Latitude, Longitude,
111151.29341326*SQRT(pow(-6-`Latitude`,2)
+pow(106-`Longitude`,2)*cos(-6*0.017453292519943)
*cos(`Latitude`*0.017453292519943)) as Distance
FROM `tablebusiness` AS TB, `tablecity` AS TC, `businessestag` AS BC,
`businessesdistricts` AS BD, `tabledistrict` AS TD,
(SELECT ID,
(SELECT Title
FROM `tablebusiness` As TBuild
WHERE TBuild.ID = TBB.Building) As BuildingTitle
FROM `tablebusiness` As TBB) AS TBuilding
WHERE (-6.0917668133836 < `Latitude` AND `Latitude` < -5.9082331866164
AND 105.90823318662 < `Longitude` AND `Longitude` < 106.09176681338)
AND TB.City = TC.City AND BC.BusinessID = TB.ID AND BD.BusinessID = TB.ID
AND TD.ID = BD.District AND TBuilding.ID = TB.ID
AND (`Title` LIKE '%%' OR `Street` LIKE '%%' OR TB.City LIKE '%%'
OR Country LIKE '%%' OR Tag LIKE '%%' OR TD.District LIKE '%%'
OR TBuilding.BuildingTitle LIKE '%%')
ORDER BY Distance LIMIT 0 ,100它仍然很慢。
那我该怎么办呢?
发布于 2012-05-31 15:46:41
你真的需要在FROM子句中包含三次tablebusiness表吗?我重写了这个并使用了JOIN。检查你的WHERE条件。Title是什么样子的'%%'?尝试这个简化的查询-
SELECT
TB.ID,
Latitude,
Longitude,
111151.29341326 * SQRT(POW(-6 - `Latitude`, 2) + POW(106 - `Longitude`, 2) * COS(-6 * 0.017453292519943) * COS(`Latitude` * 0.017453292519943)) AS Distance
FROM
`tablebusiness` AS TB
JOIN `tablecity` AS TC
ON TB.City = TC.City
JOIN `businessestag` AS BC
ON BC.BusinessID = TB.ID
JOIN `businessesdistricts` AS BD
ON BD.BusinessID = TB.ID
JOIN `tabledistrict` AS TD
ON TD.ID = BD.District
LEFT JOIN `tablebusiness` TBuilding
ON TBuilding.ID = TB.ID
WHERE
(`Title` LIKE '%%' OR `Street` LIKE '%%' OR TB.City LIKE '%%' OR Country LIKE '%%'
OR Tag LIKE '%%' OR TD.District LIKE '%%' OR TBuilding.BuildingTitle LIKE '%%')
AND (-6.0917668133836 < `Latitude` AND `Latitude` < -5.9082331866164
AND 105.90823318662 < `Longitude` AND `Longitude` < 106.09176681338)
ORDER BY
Distance
LIMIT
0, 100在JOIN和WHERE子句中使用的字段上添加索引也可以加快查询速度。
发布于 2012-05-31 15:24:50
只有使用索引才能搜索查询的某些部分。所以你必须要有一个索引。由于MySQL具有(地理)空间数据的扩展,因此您应该查找它并使用R树索引。这是两列范围限制中最有效的一种。
发布于 2012-05-31 15:27:20
在经度和经度列上有BTree索引吗?
https://stackoverflow.com/questions/10829018
复制相似问题