0

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

I want to find the nearest Point of intereset (poi_id) of certain category (cat_id) from a coordinates point (MYLAT, MYLON)

TABLE of POI (poi): 

 | poi_id | name | city_id | lat | lon |

TABLE of correlations (corr):

| cat_id | poi_id | city_id |

TABLE of CITIES (city)

| city_id | name |

SCRIPT:

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

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

Bounding box:

$lon1 = $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);

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

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)
Luca N.
  • 71
  • 1
  • 2
  • Distance is being computed for 6924 rows. If you might be able to get more performance out of it if you can use other attributes to reduce the size of the set, but short of this you may want to find another option to handle these kind of queries. – datasage Mar 04 '13 at 17:47
  • those operations are CPU intensive/bound. Asuming query is not optimised by MySQL, can you calculate `SIN($lat * PI() / 180)`, `COS($lat * PI() / 180)` in PHP and substitute float values in Query. See if it makes any difference? – जलजनक Mar 04 '13 at 18:04
  • Let me check if we can do math ops with less precision. – जलजनक Mar 04 '13 at 18:06
  • With MySQL Spatial Extensions [Fastest Way to Find Distance Between Two Lat/Long Points](http://stackoverflow.com/questions/1006654/fastest-way-to-find-distance-between-two-lat-long-points). – जलजनक Mar 04 '13 at 18:16
  • if you're not interested in actual distance then find nearest point of interest by `D = pow(abs(table.lat - mylat), 2) + pow(abs(table.lon - mylon), 2)`. This doesn't give you actual distance but gets you nearest points nonetheless **Roughly**. – जलजनक Mar 04 '13 at 18:36
  • You can calculate the actual distance when a location of interest is selected. – जलजनक Mar 04 '13 at 18:39
  • When i do "Show processlist" the status is "Copying to tmp table" any other suggestion? – Luca N. Mar 04 '13 at 20:23
  • I'v pasted the "show profile" output – Luca N. Mar 04 '13 at 21:21

0 Answers0