Here's the table definition, in case that helps:
| qs | CREATE TABLE `qs` (
`id` mediumint(8) unsigned NOT NULL auto_increment,
`province` enum('BC','AB','SK','MB') collate latin1_general_ci NOT NULL,
`s_ts_r_m` varchar(15) collate latin1_general_ci NOT NULL,
`quartersection` varchar(3) collate latin1_general_ci NOT NULL,
`latitude` decimal(8,6) NOT NULL,
`longitude` decimal(10,6) NOT NULL,
`coordinates` point NOT NULL,
PRIMARY KEY (`id`),
KEY `s_ts_r_m` (`s_ts_r_m`),
KEY `latlng` (`latitude`,`longitude`),
SPATIAL KEY `coord` (`coordinates`)
) ENGINE=MyISAM AUTO_INCREMENT=1467939 DEFAULT CHARSET=latin1
COLLATE=latin1_general_ci |
On 2009-12-31, at 9:25 PM, René Fournier wrote:
> OK, this problem (for me at least) is becoming a dead horse which I beat
> daily. I was having problems, I thought, with a spatial query running
> ridiculously slowly. Turns out the previous non-spatial index query I was
> using is also running super slow for reasons I can't figure out. So, to recap:
>
> I'm running a basic query (indexed, no joins) on a table with 1.5 million
> rows, returning ~80 rows. Executes in 45-75 seconds. On other, different
> tables with indexes I typically see queries run in 0.01 seconds. So something
> wrong. Explain:
>
> mysql> EXPLAIN SELECT id, province, latitude, longitude, AsText(coordinates),
> s_ts_r_m, quartersection, SQRT( POW( 69.1 * ( latitude - 50.444936 ) , 2 ) +
> POW( 69.1 * ( -103.813919 - longitude ) * COS( latitude / 57.3 ) , 2 ) ) AS
> distance FROM qs WHERE latitude BETWEEN 50.41949251 AND 50.47036582 AND
> longitude BETWEEN -103.85384082 AND -103.77395424\G;
> *************************** 1. row ***************************
> id: 1
> select_type: SIMPLE
> table: qs
> type: range
> possible_keys: latlng
> key: latlng
> key_len: 9
> ref: NULL
> rows: 10434
> Extra: Using where
> 1 row in set (0.00 sec)
>
>
> mysql> DESCRIBE qs;
> +----------------+---------------------------+------+-----+---------+----------------+
> | Field | Type | Null | Key | Default | Extra
> |
> +----------------+---------------------------+------+-----+---------+----------------+
> | id | mediumint(8) unsigned | NO | PRI | NULL |
> auto_increment |
> | province | enum('BC','AB','SK','MB') | NO | | NULL |
> |
> | s_ts_r_m | varchar(15) | NO | MUL | NULL |
> |
> | quartersection | varchar(3) | NO | | NULL |
> |
> | latitude | decimal(8,6) | NO | MUL | NULL |
> |
> | longitude | decimal(10,6) | NO | | NULL |
> |
> | coordinates | point | NO | MUL | NULL |
> |
> +----------------+---------------------------+------+-----+---------+----------------+
> 7 rows in set (0.00 sec)
>
> What am I missing?
>
> ...Rene
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub...@renefournier.com
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
