It sounds like your laptop might be paging mysql's memory to disk or something like that. Your laptop may not be the most reliable source for benchmarks.
Regards, Gavin Towey -----Original Message----- From: René Fournier [mailto:m...@renefournier.com] Sent: Monday, December 28, 2009 2:16 AM To: René Fournier Cc: mysql Subject: Re: Why does this query take so long? 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=gto...@ffn.com This message contains confidential information and is intended only for the individual named. If you are not the named addressee, you are notified that reviewing, disseminating, disclosing, copying or distributing this e-mail is strictly prohibited. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. E-mail transmission cannot be guaranteed to be secure or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, or contain viruses. The sender therefore does not accept liability for any loss or damage caused by viruses or errors or omissions in the contents of this message, which arise as a result of e-mail transmission. [FriendFinder Networks, Inc., 220 Humbolt court, Sunnyvale, CA 94089, USA, FriendFinder.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
