List:General Discussion« Previous MessageNext Message »
From:René Fournier Date:December 29 2009 12:09am
Subject:Re: Why does this query take so long?
View as plain text  
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: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