Simple query takes forever

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 |=20
| province | enum('BC','AB','SK','MB') | NO | | NULL | =
|=20
| s_ts_r_m | varchar(15) | NO | MUL | NULL | =
|=20
| quartersection | varchar(3) | NO | | NULL | =
|=20
| latitude | decimal(8,6) | NO | MUL | NULL | =
|=20
| longitude | decimal(10,6) | NO | | NULL | =
|=20
| coordinates | point | NO | MUL | NULL | =
|=20
=
+----------------+---------------------------+------+-----+- --------+-----=
-----------+
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=3Dgcdmg-mysql-2@m.gmane.o rg

Re: Simple query takes forever

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=3DMyISAM AUTO_INCREMENT=3D1467939 DEFAULT CHARSET=3Dlatin1 =
COLLATE=3Dlatin1_general_ci |=20


On 2009-12-31, at 9:25 PM, Ren=E9 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:
>=20
> 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:
>=20
> 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)
>=20
>=20
> mysql> DESCRIBE qs;
> =
+----------------+---------------------------+------+-----+- --------+-----=
-----------+
> | Field | Type | Null | Key | Default | =
Extra |
> =
+----------------+---------------------------+------+-----+- --------+-----=
-----------+
> | id | mediumint(8) unsigned | NO | PRI | NULL | =
auto_increment |=20
> | province | enum('BC','AB','SK','MB') | NO | | NULL | =
|=20
> | s_ts_r_m | varchar(15) | NO | MUL | NULL | =
|=20
> | quartersection | varchar(3) | NO | | NULL | =
|=20
> | latitude | decimal(8,6) | NO | MUL | NULL | =
|=20
> | longitude | decimal(10,6) | NO | | NULL | =
|=20
> | coordinates | point | NO | MUL | NULL | =
|=20
> =
+----------------+---------------------------+------+-----+- --------+-----=
-----------+
> 7 rows in set (0.00 sec)
>=20
> What am I missing?
>=20
> ...Rene
>=20
>=20
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: =
http://lists.mysql.com/mysql?unsub=3Dm5@renefournier.com
>=20


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: Simple query takes forever

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=gcdmg-mysql-2@m.gmane.org