Geek asks and answers

MYSQL geological exploration with remote performance

I have a mysql select statement to search on my site that has performance issues when the site is really busy. The query below searches for ads from a table with more than 100 thousand records, within 25 miles of a given lat value and longer and sorted by distance. The number of miles may vary as the user selects it.

The problem is that I think it is slow because it makes calculations for all entries in the table, and not for those that are within 25 miles of the lat and bosom. Is it possible to modify this request so that where the offer selects only advertisements within 25 miles? Ive read about bounding boxes and spatial indexes, but I'm not sure how to apply them to this query, I need to add a where clause that selects records with a radius of 25 miles from lat and lon, how to do it?

SELECT 
    adverts.*, 
    round(sqrt((((adverts.latitude - '53.410778') * (adverts.latitude - '53.410778')) * 69.1 * 69.1) + ((adverts.longitude - '-2.97784') * (adverts.longitude - '-2.97784') * 53 * 53)), 1) as distance
FROM 
    adverts
WHERE 
    (adverts.type_id = '3')
HAVING 
    DISTANCE < 25
ORDER BY 
    distance ASC 
LIMIT 120,10

Edit: updated to include the table schema, note that the table is more complex, so there is a query, but I deleted things that are not needed for this problem.

CREATE TABLE `adverts` (
`advert_id` int(10) NOT NULL AUTO_INCREMENT,
`type_id` tinyint(1) NOT NULL,
`headline` varchar(50) NOT NULL,
`description` text NOT NULL,
`price` int(4) NOT NULL,
`postcode` varchar(7) NOT NULL,
`latitude` float NOT NULL,
`longitude` float NOT NULL,
PRIMARY KEY (`advert_id`),
KEY `latlon` (`latitude`,`longitude`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

mysql, 67900, , 25 , "Using where; Using filesort".

0,3 , , - .

+5
2

- MySQL, , MyISAM. : http://dev.mysql.com/doc/refman/5.6/en/spatial-extensions.html

POINT:

ALTER TABLE `adverts` 
ADD COLUMN `geopoint` POINT NOT NULL AFTER `longitude`
ADD SPATIAL KEY `geopoint` (`geopoint`)

:

UPDATE `adverts` 
SET `geopoint` = GeomFromText(CONCAT('POINT(',`latitude`,' ',`longitude`,')'));

- , WHERE CONTAINS. X, Y POINT, .

POINT , POLYGON, :

SELECT a.*, 
    ROUND( SQRT( ( ( (adverts.latitude - '53.410778') * (adverts.latitude - '53.410778') ) * 69.1 * 69.1 ) + ( (adverts.longitude - '-2.97784') * (adverts.longitude - '-2.97784') * 53 * 53 ) ), 1 ) AS distance
FROM adverts a
WHERE a.type_id = 3
AND CONTAINS(a.geopoint, GeomFromText('Polygon((0 0,0 3,3 3,3 0,0 0))'))
HAVING distance < 25
ORDER BY distance DESC
LIMIT 0, 30

, GeomFromText('Polygon((0 0,0 3,3 3,3 0,0 0))') , . lat/long, , POINT SPATIAL KEY. HAVING. POLYGON.

+5

, POW.

X, Y.

, .

SELECT a .* , 
    round( sqrt( 
        (POW( a.latitude -'53.410778', 2)* 68.1 * 68.1) + 
        (POW(a.latitude -'-2.97784', 2) * 53.1 * 53.1) 
     )) AS distance
 FROM adverts a
     WHERE a.type_id = 3
     HAVING distance < 25
     LIMIT 0 , 30

0.0008 sec 10,000 ( , , 0.0129 sec), .

  • SQL- , SELECT *.
  • mydatabase.mytable.
  • ORDER BY primary key ( ) index , ORDERING).
  • mysql framework , .
  • , , ( , ).

+3

More articles:


All Articles