Okay, now that I have your attention. :-) What I actually mean is,
Spatial Extensions in MySQL 5.0.16 (the version I'm on) seem so slow
as to appear broken. Why:
I'm trying to speed up a query that fetches the nearest 10 records
(essentially, latitude/longitude pairs, stored in a Geometry point
column) to a given point (latitude/longitude). Here's the query I'm
using right now, which works but is slow:
SELECT latitude, longitude, GLength(LineStringFromWKB(LineString
(AsBinary(coordinates), AsBinary(GeomFromText('POINT(51 -114)')))))
AS distance FROM places ORDER BY distance ASC LIMIT 10
latitude longitude distance
51.00137160 -114.00182421 0.0022823296615694
50.99412759 -114.00182513 0.0061494958106356
51.00859980 -114.00181734 0.0087897260887692
...
It takes about 2.3 seconds to execute on a MacBook Pro. Now, the
table is big--over 800,000 rows. And the above query is a one-second
improvement over this original one:
SELECT latitude, longitude, SQRT( POW( 69.1 * ( latitude - 51) , 2 )
+ POW( 69.1 * (-114 - longitude) * COS( latitude / 57.3 ) , 2 ) ) AS
distance FROM places ORDER BY distance ASC LIMIT 0,10
But I expected much better. I created a spatial index on the
coordinates column, but it is not being used (I did EXPLAIN). This is
not surprising, since there's a calculation that needs to be
performed on every single row. But is there a faster way to fetch the
closest records to a given point? The MySQL docs are incredibly terse
and I can't find any other examples or code to copy.
Any help is much appreciated.
...Rene
PS: For clarity, here is the table structure:
CREATE TABLE `places` (
`id` mediumint(8) unsigned NOT NULL auto_increment,
`latitude` decimal(10,8) NOT NULL,
`longitude` decimal(12,8) NOT NULL,
`coordinates` point NOT NULL,
PRIMARY KEY (`id`),
SPATIAL KEY `latlng` (`coordinates`(32))
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci
AUTO_INCREMENT=845891 ;
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
