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
>
