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/[email protected]
