Why does this query take so long?

--Apple-Mail-2-129307146
Content-Transfer-Encoding: quoted-printable
Content-Type: text/plain;
charset=us-ascii

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 |=20
=
+----+-------------+-------+-------+---------------+-------+ ---------+----=
--+------+-------------+
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 |=20
| 444564 | AB | 51.64941120 | -114.79283278 | POINT(51.6494112 =
-114.79283278) | 09-031-06 W5 | N4 |=20
| 444548 | AB | 51.63497789 | -114.81645649 | POINT(51.63497789 =
-114.81645649) | 05-031-06 W5 | N4 |=20
| 444561 | AB | 51.64943119 | -114.81643801 | POINT(51.64943119 =
-114.81643801) | 08-031-06 W5 | N4 |=20
| 444547 | AB | 51.62775680 | -114.80475858 | POINT(51.6277568 =
-114.80475858) | 05-031-06 W5 | E4 |=20
| 444549 | AB | 51.63498028 | -114.80479925 | POINT(51.63498028 =
-114.80479925) | 05-031-06 W5 | NE |=20
| 444560 | AB | 51.64220442 | -114.80478262 | POINT(51.64220442 =
-114.80478262) | 08-031-06 W5 | E4 |=20
| 444562 | AB | 51.64942854 | -114.80476596 | POINT(51.64942854 =
-114.80476596) | 08-031-06 W5 | NE |=20
=
+--------+----------+-------------+---------------+--------- --------------=
-----------+--------------+----------------+
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=

--Apple-Mail-2-129307146--

Re: Why does this query take so long?

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 |=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)

8 rows in set (3.87 sec)


On 2009-12-27, at 3:59 PM, Ren=E9 Fournier wrote:

> 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 |=20
> =
+----+-------------+-------+-------+---------------+-------+ ---------+----=
--+------+-------------+
> 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 |=20
> | 444564 | AB | 51.64941120 | -114.79283278 | POINT(51.6494112 =
-114.79283278) | 09-031-06 W5 | N4 |=20
> | 444548 | AB | 51.63497789 | -114.81645649 | POINT(51.63497789 =
-114.81645649) | 05-031-06 W5 | N4 |=20
> | 444561 | AB | 51.64943119 | -114.81643801 | POINT(51.64943119 =
-114.81643801) | 08-031-06 W5 | N4 |=20
> | 444547 | AB | 51.62775680 | -114.80475858 | POINT(51.6277568 =
-114.80475858) | 05-031-06 W5 | E4 |=20
> | 444549 | AB | 51.63498028 | -114.80479925 | POINT(51.63498028 =
-114.80479925) | 05-031-06 W5 | NE |=20
> | 444560 | AB | 51.64220442 | -114.80478262 | POINT(51.64220442 =
-114.80478262) | 08-031-06 W5 | E4 |=20
> | 444562 | AB | 51.64942854 | -114.80476596 | POINT(51.64942854 =
-114.80476596) | 08-031-06 W5 | NE |=20
> =
+--------+----------+-------------+---------------+--------- --------------=
-----------+--------------+----------------+
> 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


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: Why does this query take so long?

Hmm, weird. I just re-imported the data (after drop/create table, etc.), =
and now the spatial queries run fast.=20
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=E9 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?).
>=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
> =
+----+-------------+-------+-------+---------------+-------+ ---------+----=
--+------+-------------+
>=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 |=20
>> =
+----+-------------+-------+-------+---------------+-------+ ---------+----=
--+------+-------------+
>> 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 |=20
>> | 444564 | AB | 51.64941120 | -114.79283278 | POINT(51.6494112 =
-114.79283278) | 09-031-06 W5 | N4 |=20
>> | 444548 | AB | 51.63497789 | -114.81645649 | POINT(51.63497789 =
-114.81645649) | 05-031-06 W5 | N4 |=20
>> | 444561 | AB | 51.64943119 | -114.81643801 | POINT(51.64943119 =
-114.81643801) | 08-031-06 W5 | N4 |=20
>> | 444547 | AB | 51.62775680 | -114.80475858 | POINT(51.6277568 =
-114.80475858) | 05-031-06 W5 | E4 |=20
>> | 444549 | AB | 51.63498028 | -114.80479925 | POINT(51.63498028 =
-114.80479925) | 05-031-06 W5 | NE |=20
>> | 444560 | AB | 51.64220442 | -114.80478262 | POINT(51.64220442 =
-114.80478262) | 08-031-06 W5 | E4 |=20
>> | 444562 | AB | 51.64942854 | -114.80476596 | POINT(51.64942854 =
-114.80476596) | 08-031-06 W5 | NE |=20
>> =
+--------+----------+-------------+---------------+--------- --------------=
-----------+--------------+----------------+
>> 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


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

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=E9 Fournier wrote:

> Hmm, weird. I just re-imported the data (after drop/create table, =
etc.), and now the spatial queries run fast.=20
> 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
>> =
+----+-------------+-------+-------+---------------+-------+ ---------+----=
--+------+-------------+
>>=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 |=20
>>> =
+----+-------------+-------+-------+---------------+-------+ ---------+----=
--+------+-------------+
>>> 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 |=20
>>> | 444564 | AB | 51.64941120 | -114.79283278 | POINT(51.6494112 =
-114.79283278) | 09-031-06 W5 | N4 |=20
>>> | 444548 | AB | 51.63497789 | -114.81645649 | =
POINT(51.63497789 -114.81645649) | 05-031-06 W5 | N4 |=20
>>> | 444561 | AB | 51.64943119 | -114.81643801 | =
POINT(51.64943119 -114.81643801) | 08-031-06 W5 | N4 |=20
>>> | 444547 | AB | 51.62775680 | -114.80475858 | POINT(51.6277568 =
-114.80475858) | 05-031-06 W5 | E4 |=20
>>> | 444549 | AB | 51.63498028 | -114.80479925 | =
POINT(51.63498028 -114.80479925) | 05-031-06 W5 | NE |=20
>>> | 444560 | AB | 51.64220442 | -114.80478262 | =
POINT(51.64220442 -114.80478262) | 08-031-06 W5 | E4 |=20
>>> | 444562 | AB | 51.64942854 | -114.80476596 | =
POINT(51.64942854 -114.80476596) | 08-031-06 W5 | NE |=20
>>> =
+--------+----------+-------------+---------------+--------- --------------=
-----------+--------------+----------------+
>>> 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@renefournier.com
>=20


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

RE: Why does this query take so long?

It sounds like your laptop might be paging mysql's memory to disk or someth=
ing like that. Your laptop may not be the most reliable source for benchma=
rks.

Regards,
Gavin Towey

-----Original Message-----
From: Ren=E9 Fournier [mailto:m5@renefournier.com]
Sent: Monday, December 28, 2009 2:16 AM
To: Ren=E9 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 sa=
me queries are taking 3-10 seconds instead of 0.01 seconds. What could be c=
ausing this?

On 2009-12-28, at 1:19 PM, Ren=E9 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 in=
dicated it would use the Spatial Index)?
>
>
>
> On 2009-12-28, at 9:28 AM, Ren=E9 Fournier wrote:
>
>> So just to clarify (hello?), the index which *should* be used (EXPLAIN s=
ays 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 | r=
ef | rows | Extra |
>> +----+-------------+-------+-------+---------------+-------+ ---------+--=
----+------+-------------+
>> | 1 | SIMPLE | qs | range | coord | coord | 27 | N=
ULL | 5260 | Using where |
>> +----+-------------+-------+-------+---------------+-------+ ---------+--=
----+------+-------------+
>>
>> SELECT id, province, latitude, longitude, AsText(coordinates), s_ts_r_m,=
quartersection FROM qs WHERE MBRContains(GeomFromText('POLYGON((51.6258258=
9 -114.82248918,51.65126254 -114.82248918,51.65126254 -114.78150333,51.6258=
2589 -114.78150333,51.62582589 -114.82248918))'), coordinates)
>>
>> 8 rows in set (3.87 sec)
>>
>>
>> On 2009-12-27, at 3:59 PM, Ren=E9 Fournier wrote:
>>
>>> So... there is an index, and it's supposedly used:
>>>
>>> mysql> EXPLAIN SELECT id, province, latitude, longitude, AsText(coordin=
ates), s_ts_r_m, quartersection FROM qs WHERE MBRContains(GeomFromText('POL=
YGON((51.62582589 -114.82248918,51.65126254 -114.82248918,51.65126254 -114.=
78150333,51.62582589 -114.78150333,51.62582589 -114.82248918))'), coordinat=
es);
>>> +----+-------------+-------+-------+---------------+-------+ ---------+-=
-----+------+-------------+
>>> | 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 -1=
14.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 -1=
14.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 spatia=
lly-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=3Dm5@renefournier.c=
om
>


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


This message contains confidential information and is intended only for the=
individual named. If you are not the named addressee, you are notified th=
at reviewing, disseminating, disclosing, copying or distributing this e-mai=
l is strictly prohibited. Please notify the sender immediately by e-mail i=
f 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-fre=
e as information could be intercepted, corrupted, lost, destroyed, arrive l=
ate 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 transmis=
sion. [FriendFinder Networks, Inc., 220 Humbolt court, Sunnyvale, CA 94089,=
USA, FriendFinder.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: Why does this query take so long?

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@renefournier.com]
> 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@renefournier.com
>>=20
>=20
>=20
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgtowey@ffn.com
>=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


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: Why does this query take so long?

Hi Ren=E9, everybody!


Ren=E9 Fournier wrote:
> [[...]] However, even if the Index can't fit in memory (4GB of RAM,=
lots free), just reading it from disk should allow sub-millisecond r=
esponse, no?

No chance!
Of course, performance of disk drives varies, but AFAIK typical value=
s
are in the range of 7 - 10 ms per random access.
Assuming the (very unlikely) optimum case of one index access and one
data access, this would put you into the range of 15 - 20 ms just for
fetching the stuff from disk, not including any CPU time to traverse =
the
data structures etc.

Just do some math:
A disk with 7,200 rpm has 120 revolutions per second, so it needs a b=
it
more than 8 milliseconds per revolution.
Random access means you have to wait (on average) for half a revoluti=
on
(4 ms) until the desired block passes the disk head, and before that =
the
head needs to be positioned at the proper cylinder (the drive's data
sheet might give that time).

I guess that even with SSD you will not reach sub-millisecond respons=
e
times if the data is not in RAM.

>=20
> 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... ne=
w.

IMO, the most influential factor in single-user database benchmarks a=
re
- disk performance
- RAM size for caches, cache replacement
- history, cache preloading

Their relative importance will vary, depending especially on data siz=
e.
As long as your data size is small enough that RAM differences betwee=
n
server and laptop don't matter too much, performance on the laptop ma=
y
be a good prediction of that on the server.

With multi-user benchmarks, CPU performance, number of cores etc beco=
mes
another important factor, again the relative weights will vary.


Regards,
Jörg

--=20
Joerg Bruehe, MySQL Build Team, Joerg.Bruehe@Sun.COM
Sun Microsystems GmbH, Komturstraße 18a, D-12099 Berlin
Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels, Wolf Frenkel
Vorsitzender des Aufsichtsrates: Martin Haering Muenchen: HRB1610=
28


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg