From: René Fournier (m5renefournier.com)
Date: Thu Dec 31 2009 - 08:37:25 CST

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

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=m5renefournier.com
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]