<bdo id='fRP9O'></bdo><ul id='fRP9O'></ul>

    1. <i id='fRP9O'><tr id='fRP9O'><dt id='fRP9O'><q id='fRP9O'><span id='fRP9O'><b id='fRP9O'><form id='fRP9O'><ins id='fRP9O'></ins><ul id='fRP9O'></ul><sub id='fRP9O'></sub></form><legend id='fRP9O'></legend><bdo id='fRP9O'><pre id='fRP9O'><center id='fRP9O'></center></pre></bdo></b><th id='fRP9O'></th></span></q></dt></tr></i><div id='fRP9O'><tfoot id='fRP9O'></tfoot><dl id='fRP9O'><fieldset id='fRP9O'></fieldset></dl></div>
    2. <small id='fRP9O'></small><noframes id='fRP9O'>

    3. <legend id='fRP9O'><style id='fRP9O'><dir id='fRP9O'><q id='fRP9O'></q></dir></style></legend>
      <tfoot id='fRP9O'></tfoot>
    4. 为 MySQL 半径内的点查找大表的最快方法是什么(经度纬度)

      时间:2023-06-01
          <tbody id='KRq60'></tbody>

        <small id='KRq60'></small><noframes id='KRq60'>

            <bdo id='KRq60'></bdo><ul id='KRq60'></ul>
            <i id='KRq60'><tr id='KRq60'><dt id='KRq60'><q id='KRq60'><span id='KRq60'><b id='KRq60'><form id='KRq60'><ins id='KRq60'></ins><ul id='KRq60'></ul><sub id='KRq60'></sub></form><legend id='KRq60'></legend><bdo id='KRq60'><pre id='KRq60'><center id='KRq60'></center></pre></bdo></b><th id='KRq60'></th></span></q></dt></tr></i><div id='KRq60'><tfoot id='KRq60'></tfoot><dl id='KRq60'><fieldset id='KRq60'></fieldset></dl></div>
          • <legend id='KRq60'><style id='KRq60'><dir id='KRq60'><q id='KRq60'></q></dir></style></legend>

            <tfoot id='KRq60'></tfoot>

                本文介绍了为 MySQL 半径内的点查找大表的最快方法是什么(经度纬度)的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着跟版网的小编来一起学习吧!

                问题描述

                目前我有几个表有 100k+ 行.我正在尝试查找如下数据.

                SELECT*, SQRT(POW(69.1 * (latitude - '49.1044302'), 2) + POW(69.1 * ('-122.801094' - longitude) * COS(latitude/57.3), 2)) AS距离从站有距离<5ORDER BY 距离限制 100

                但目前这种方法会因高负载而变慢.有些查询需要 20 多秒才能完成.

                如果有人知道任何更好的优化方法就太好了.

                解决方案

                首先,如果你有大量的地理空间数据,你应该使用 mysql 的地理空间扩展而不是这样的计算.然后您可以创建空间索引,这将加快很多查询,您不必像上面那样编写冗长的查询.

                使用与 ST_Distance 或创建具有感兴趣半径的几何体以及 ST_within 可能会给您带来良好的结果,并且可能比当前快得多.然而,实现此目标的最佳和最快方法是 ST_Dwithin 尚未在 mysql 中实现.>

                Currently I have a few tables with 100k+ rows. I am trying to lookup the data like follows.

                SELECT
                *, SQRT(POW(69.1 * (latitude - '49.1044302'), 2) + POW(69.1 * ('-122.801094' - longitude) * COS(latitude / 57.3), 2)) AS distance
                FROM stops
                HAVING distance < 5
                ORDER BY distance limit 100
                

                But currently this method slows with high load. Some queries are taking 20+ seconds to complete.

                If anyone knows any better ways to optimize this would be great.

                解决方案

                Well first of all if you have a lot of geospatial data, you should be using mysql's geospatial extensions rather than calculations like this. You can then create spatial indexes that would speed up many queries and you don't have to write long drawn out queries like the one above.

                Using a comparision with ST_Distance or creating a geometry with the radius of interest along with ST_within might give you good results and could be a lot faster than the current. However the best and fastest way to achieve this, ST_Dwithin isn't implemented yet in mysql.

                这篇关于为 MySQL 半径内的点查找大表的最快方法是什么(经度纬度)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!

                上一篇:任何在 InnoDB 上实现类全文搜索的方法 下一篇:MySQL 数据透视表 - 行到列.询问

                相关文章

                1. <i id='b9OTO'><tr id='b9OTO'><dt id='b9OTO'><q id='b9OTO'><span id='b9OTO'><b id='b9OTO'><form id='b9OTO'><ins id='b9OTO'></ins><ul id='b9OTO'></ul><sub id='b9OTO'></sub></form><legend id='b9OTO'></legend><bdo id='b9OTO'><pre id='b9OTO'><center id='b9OTO'></center></pre></bdo></b><th id='b9OTO'></th></span></q></dt></tr></i><div id='b9OTO'><tfoot id='b9OTO'></tfoot><dl id='b9OTO'><fieldset id='b9OTO'></fieldset></dl></div>

                  <legend id='b9OTO'><style id='b9OTO'><dir id='b9OTO'><q id='b9OTO'></q></dir></style></legend>

                  <small id='b9OTO'></small><noframes id='b9OTO'>

                  <tfoot id='b9OTO'></tfoot>
                  • <bdo id='b9OTO'></bdo><ul id='b9OTO'></ul>