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

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