mysql lat long_Mysql cpu用法增加排序lat lon距离最近点

问题是：为什么mysql的cpu使用率达到300％ - ＆gt; 当我在我的脚本php中添加此查询时，400％？

the question is: Why mysql's cpu usage goes to 300% -》 400% when i add this query in my script php ?

我想从坐标点(MYLAT，MYLON)找到某个类别(cat\_id)的最近兴趣点(poi id)

I want to find the nearest Point of intereset (poi\_id) of certain category (cat\_id) from a coordinates point (MYLAT, MYLON)

POI (poi)表：

TABLE of **POI** (poi):

| poi_id | name | city_id | lat | lon |

**相关性表**(corr)：

TABLE of **correlations** (corr):

| cat_id | poi_id | city_id |

表**城市**(城市)

TABLE of **CITIES** (city)

| city_id | name |

**脚本：**

**SCRIPT:**

$ lat = **MYLAT** ; //我的起点的纬度

$lat = **MYLAT**; // Latitude of my starting point

$ lon = **MYLON** ; //我的起点的经度

$lon = **MYLON**; // Longitude of my starting point

**包围框**：

**Bounding box**:

$ lon1 = $ lon - ($ dist / abs(cos(rad2deg($ lat))\* 60 \* 1.1515));

$lon1 = $lon - ($dist/abs(cos(rad2deg($lat)) \* 60 \* 1.1515));

$ lon2 = $ lon +($ dist / abs(cos(rad2deg($ lat))\* 60 \* 1.1515));

$lon2 = $lon + ($dist/abs(cos(rad2deg($lat)) \* 60 \* 1.1515));

$ lat1 = $ lat - ($ dist / 60 \* 1.1515);

$lat1 = $lat - ($dist/ 60 \* 1.1515);

$ lat2 = $ lat +($ dist / 60 \* 1.1515);

$lat2 = $lat + ($dist/ 60 \* 1.1515);

当我添加此查询时，CPU使用率变得疯狂：

When I add this query the CPU usage goes insane:

SELECT a.poi_id,a.name,a.city_id,g.name,a.lat, a.lon,

((ACOS(SIN($lat * PI() / 180) * SIN(a.lat * PI() / 180) +

COS($lat * PI() / 180) * COS(a.lat * PI() / 180) *

COS(($lon - a.lon) * PI() / 180)) * 180 / PI()) * 69.09) AS distance

FROM poi a JOIN corr c on a.poi_id = c.poi_id JOIN city g on c.city_id = g.city_id

WHERE a.lon 》 $lon1 and a.lon 《 $lon2

AND a.lat 》 $lat1 and a.lat 《 $lat2

AND c.cat_id = $cat_id

HAVING distance 《 $dist

ORDER BY distance

LIMIT 20

+----+-------------+-------+--------+----------------------------------+---------+---------+--------------------+------+-----------------------------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+-------------+-------+--------+----------------------------------+---------+---------+--------------------+------+-----------------------------+

| 1 | SIMPLE | a | range | poi_id,lat,lon,lat_2 | lat_2 | 14 | NULL | 6924 | Using where; Using filesort |

| 1 | SIMPLE | c | eq_ref | city_id_2,cat_id,poi_id,city_id | cat_id | 5 | const,a.poi_id | 1 | |

| 1 | SIMPLE | g | eq_ref | PRIMARY | PRIMARY | 4 | c.city_id | 1 | Using where |

+----+-------------+-------+--------+----------------------------------+---------+---------+--------------------+------+-----------------------------+

mysql》 show profile;

+----------------------+----------+

| Status | Duration |

+----------------------+----------+

| starting | 0.000138 |

| checking permissions | 0.000010 |

| checking permissions | 0.000006 |

| checking permissions | 0.000008 |

| Opening tables | 0.000028 |

| System lock | 0.000015 |

| init | 0.000043 |

| optimizing | 0.000023 |

| statistics | 0.000221 |

| preparing | 0.000024 |

| Creating tmp table | 0.000029 |

| executing | 0.000005 |

| Copying to tmp table | 0.174197 |

| Sorting result | 0.000066 |

| Sending data | 0.000065 |

| end | 0.000006 |

| removing tmp table | 0.000010 |

| end | 0.000006 |

| query end | 0.000005 |

| closing tables | 0.000011 |

| freeing items | 0.000077 |

| logging slow query | 0.000005 |

| cleaning up | 0.000006 |

+----------------------+----------+

23 rows in set (0.00 sec)