根据长期查找用户 [英] Find User on Basis of Lat long

问题描述

我想查找位置在5英里以下且具有与我相同标签的用户.

I want to find users which are under 5 miles location and having the same tag that I have.

我的数据结构:

UserTable

--------------------------------------
 userid | name | lat | long | address
--------------------------------------
  101   |  xyz |  92 |  72  | NY
  201   |  HYS |  48 |  56  | JAMAICA
  301   |  LMN |  92 |  75  | Brazil


TagTable

---------------------
 id | userid | tagid
---------------------
  1 |   101  |   5
  2 |   201  |   7
  3 |   301  |   5

查询:

SELECT vb.userid,
       vb.address,
       ( 6371 * ACOS(   COS( RADIANS( 28.684342 ) )
                          * COS( RADIANS( vb.lat ) ) 
                          * COS( RADIANS( vb.long) - RADIANS( 77.137941 ) )
                      + SIN( RADIANS( 28.684342 ) )
                          * SIN( RADIANS( vb.lat) )
                    )
       ) AS distance
  FROM UserTable vb, TagTable vk
 WHERE vk.userid = vb.userid
   AND vk.tagid = '5'
 GROUP BY vk.userid
HAVING distance < 10
 ORDER BY distance;

上面的查询要花很多时间，请帮助我找到这种情况下最快的查询.

The above query is taking lot of time, kindly help me to find the fastest query for this scenario.

推荐答案

如果索引编制没有太大的不同，我可以尝试采取一些繁重的计算步骤，看看如何提高效率:

If indexing doesn't make a big difference I might try and take some of the heavy computational steps out, and see how that improves efficiency:

想法1:删除ORDER BY子句，然后在PHP中排序.

Idea 1: remove the ORDER BY clause, and instead sort in PHP.

想法2:然后还删除HAVING子句，而是在遍历结果时在PHP中进行过滤.

Idea 2: then also remove the HAVING clause, and instead filter in PHP as you loop through the results.

想法3:如果userid对于TagTable中的特定tagid只能出现一次，请删除整个GROUP BY子句，并将距离测试添加到WHERE子句中.

Idea 3: If a userid can only appear once for a particular tagid in TagTable, remove the whole GROUP BY clause, and add the distance test into the WHERE clause.

我不确定将某些处理传递给PHP是否会提高效率，因为至少值得进行测试.

I'm not sure if passing off some of the processing to PHP would help efficiency, by it's worth testing at least.

这篇关于根据长期查找用户的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！