首页
学习
活动
专区
圈层
工具
发布
社区首页 >问答首页 >如何提高查询速度

问如何提高查询速度
EN

Stack Overflow用户
提问于 2012-05-31 15:14:49
回答 4查看 111关注 0票数 1

我使用了这个查询,mysql花了太长时间才给出结果。行数为160万行。

代码语言:javascript
复制
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万部分,也就是

代码语言:javascript
复制
AND (-6.0917668133836 < `Latitude` AND `Latitude` < -5.9082331866164 AND 105.90823318662 < `Longitude` AND `Longitude` < 106.09176681338) 

极大地限制了行数。看起来mysql似乎不会费心通过搜索

代码语言:javascript
复制
AND (-6.0917668133836 < `Latitude` AND `Latitude` < -5.9082331866164 AND 105.90823318662 < `Longitude` AND `Longitude` < 106.09176681338) first.

我通过移动前面的纬度、经度方面更改了查询

代码语言:javascript
复制
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

它仍然很慢。

那我该怎么办呢?

EN

回答 4

Stack Overflow用户

回答已采纳

发布于 2012-05-31 15:46:41

你真的需要在FROM子句中包含三次tablebusiness表吗?我重写了这个并使用了JOIN。检查你的WHERE条件。Title是什么样子的'%%'?尝试这个简化的查询-

代码语言:javascript
复制
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子句中使用的字段上添加索引也可以加快查询速度。

票数 1
EN

Stack Overflow用户

发布于 2012-05-31 15:24:50

只有使用索引才能搜索查询的某些部分。所以你必须要有一个索引。由于MySQL具有(地理)空间数据的扩展,因此您应该查找它并使用R树索引。这是两列范围限制中最有效的一种。

票数 1
EN

Stack Overflow用户

发布于 2012-05-31 15:27:20

在经度和经度列上有BTree索引吗?

票数 1
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/10829018

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档