From: René Fournier Date: December 29 2009 12:09am Subject: Re: Why does this query take so long? List-Archive: http://lists.mysql.com/mysql/219963 Message-Id: MIME-Version: 1.0 (Apple Message framework v1077) Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable 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. >=20 > Regards, > Gavin Towey >=20 > -----Original Message----- > From: Ren=E9 Fournier [mailto:m5@stripped] > Sent: Monday, December 28, 2009 2:16 AM > To: Ren=E9 Fournier > Cc: mysql > Subject: Re: Why does this query take so long? >=20 > 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? >=20 > On 2009-12-28, at 1:19 PM, Ren=E9 Fournier wrote: >=20 >> 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)? >>=20 >>=20 >>=20 >> On 2009-12-28, at 9:28 AM, Ren=E9 Fournier wrote: >>=20 >>> 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?). >>>=20 >>> = +----+-------------+-------+-------+---------------+-------+---------+----= --+------+-------------+ >>> | id | select_type | table | type | possible_keys | key | key_len = | ref | rows | Extra | >>> = +----+-------------+-------+-------+---------------+-------+---------+----= --+------+-------------+ >>> | 1 | SIMPLE | qs | range | coord | coord | 27 = | NULL | 5260 | Using where | >>> = +----+-------------+-------+-------+---------------+-------+---------+----= --+------+-------------+ >>>=20 >>> 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) >>>=20 >>> 8 rows in set (3.87 sec) >>>=20 >>>=20 >>> On 2009-12-27, at 3:59 PM, Ren=E9 Fournier wrote: >>>=20 >>>> So... there is an index, and it's supposedly used: >>>>=20 >>>> 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) >>>>=20 >>>> But when I run the query: >>>>=20 >>>> 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) >>>>=20 >>>> 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? >>>>=20 >>>> ...REne >>>=20 >>=20 >>=20 >> -- >> MySQL General Mailing List >> For list archives: http://lists.mysql.com/mysql >> To unsubscribe: = http://lists.mysql.com/mysql?unsub=3Dm5@stripped >>=20 >=20 >=20 > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgtowey@stripped >=20 >=20 > 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