I think you might be right. The good-to-poor performance I'm seeing is so intermittent. And I see now that it's also with other queries, though not as extremely obvious as the spatial queries. However, even if the Index can't fit in memory (4GB of RAM, lots free), just reading it from disk should allow sub-millisecond response, no?
Strange thing is that I've used my laptop for benchmarking for the past five years and it's always produced results fairly typical or at least consistent in relation to our servers. This new thing is... new. On 2009-12-29, at 3:18 AM, Gavin Towey wrote: > 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
