List:General Discussion« Previous MessageNext Message »
From:Gavin Towey Date:December 28 2009 8:18pm
Subject:RE: Why does this query take so long?
View as plain text  
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:m5@stripped]
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=1
>


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql?unsub=1


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
Thread
Why does this query take so long?René Fournier27 Dec
  • Re: Why does this query take so long?René Fournier28 Dec
    • Re: Why does this query take so long?René Fournier28 Dec
      • Re: Why does this query take so long?René Fournier28 Dec
        • RE: Why does this query take so long?Gavin Towey28 Dec
          • Re: Why does this query take so long?René Fournier29 Dec
            • Re: Why does this query take so long?Joerg Bruehe29 Dec