At 08:25 AM 12/31/2009, you 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
Rene,
Hard to say. I'd recommend creating a temporary table with the same
structure as the old table except with lattitude and longtitude as
Double(10,6). It might have something to do with Decimal() which may be
storing the number as a string.
create table qstmp like qs;
alter table qstmp change column latitude latitude double(10,6), longitude
longitude double(10,6);
insert into qstmp select * from qs;
Now do your query.
(Sorry of there are any syntax errors)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
