Even weirder, I came back to my laptop a couple hours later. And now the same queries are taking 3-10 seconds instead of 0.01 seconds. What could be causing this?
On 2009-12-28, at 1:19 PM, René Fournier wrote: > Hmm, weird. I just re-imported the data (after drop/create table, etc.), and > now the spatial queries run fast. > Has anyone seen this sort of thing happen? Maybe the Index got corrupted > somehow, and then MySQL had to do a full table scan (even though EXPLAIN > indicated it would use the Spatial Index)? > > > > On 2009-12-28, at 9:28 AM, René Fournier wrote: > >> So just to clarify (hello?), the index which *should* be used (EXPLAIN says >> so) and *should* make the query run faster than 4 seconds either isn't used >> (why?) or simply doesn't speed up the query (again, why?). >> >> +----+-------------+-------+-------+---------------+-------+---------+------+------+-------------+ >> | id | select_type | table | type | possible_keys | key | key_len | ref >> | rows | Extra | >> +----+-------------+-------+-------+---------------+-------+---------+------+------+-------------+ >> | 1 | SIMPLE | qs | range | coord | coord | 27 | NULL >> | 5260 | Using where | >> +----+-------------+-------+-------+---------------+-------+---------+------+------+-------------+ >> >> SELECT id, province, latitude, longitude, AsText(coordinates), s_ts_r_m, >> quartersection FROM qs WHERE MBRContains(GeomFromText('POLYGON((51.62582589 >> -114.82248918,51.65126254 -114.82248918,51.65126254 >> -114.78150333,51.62582589 -114.78150333,51.62582589 -114.82248918))'), >> coordinates) >> >> 8 rows in set (3.87 sec) >> >> >> On 2009-12-27, at 3:59 PM, René Fournier wrote: >> >>> So... there is an index, and it's supposedly used: >>> >>> mysql> EXPLAIN SELECT id, province, latitude, longitude, >>> AsText(coordinates), s_ts_r_m, quartersection FROM qs WHERE >>> MBRContains(GeomFromText('POLYGON((51.62582589 -114.82248918,51.65126254 >>> -114.82248918,51.65126254 -114.78150333,51.62582589 >>> -114.78150333,51.62582589 -114.82248918))'), coordinates); >>> +----+-------------+-------+-------+---------------+-------+---------+------+------+-------------+ >>> | id | select_type | table | type | possible_keys | key | key_len | ref >>> | rows | Extra | >>> +----+-------------+-------+-------+---------------+-------+---------+------+------+-------------+ >>> | 1 | SIMPLE | qs | range | coord | coord | 27 | NULL >>> | 5260 | Using where | >>> +----+-------------+-------+-------+---------------+-------+---------+------+------+-------------+ >>> 1 row in set (0.00 sec) >>> >>> But when I run the query: >>> >>> mysql> SELECT id, province, latitude, longitude, AsText(coordinates), >>> s_ts_r_m, quartersection FROM qs WHERE >>> MBRContains(GeomFromText('POLYGON((51.62582589 -114.82248918,51.65126254 >>> -114.82248918,51.65126254 -114.78150333,51.62582589 >>> -114.78150333,51.62582589 -114.82248918))'), coordinates) >>> -> ; >>> +--------+----------+-------------+---------------+----------------------------------+--------------+----------------+ >>> | id | province | latitude | longitude | AsText(coordinates) >>> | s_ts_r_m | quartersection | >>> +--------+----------+-------------+---------------+----------------------------------+--------------+----------------+ >>> | 444543 | AB | 51.63495228 | -114.79282412 | POINT(51.63495228 >>> -114.79282412) | 04-031-06 W5 | N4 | >>> | 444564 | AB | 51.64941120 | -114.79283278 | POINT(51.6494112 >>> -114.79283278) | 09-031-06 W5 | N4 | >>> | 444548 | AB | 51.63497789 | -114.81645649 | POINT(51.63497789 >>> -114.81645649) | 05-031-06 W5 | N4 | >>> | 444561 | AB | 51.64943119 | -114.81643801 | POINT(51.64943119 >>> -114.81643801) | 08-031-06 W5 | N4 | >>> | 444547 | AB | 51.62775680 | -114.80475858 | POINT(51.6277568 >>> -114.80475858) | 05-031-06 W5 | E4 | >>> | 444549 | AB | 51.63498028 | -114.80479925 | POINT(51.63498028 >>> -114.80479925) | 05-031-06 W5 | NE | >>> | 444560 | AB | 51.64220442 | -114.80478262 | POINT(51.64220442 >>> -114.80478262) | 08-031-06 W5 | E4 | >>> | 444562 | AB | 51.64942854 | -114.80476596 | POINT(51.64942854 >>> -114.80476596) | 08-031-06 W5 | NE | >>> +--------+----------+-------------+---------------+----------------------------------+--------------+----------------+ >>> 8 rows in set (3.87 sec) >>> >>> So, there are ~2.6 million rows in the table, and coordinates is >>> spatially-indexed. Yet the query requires nearly 4 seconds. What am I doing >>> wrong? >>> >>> ...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