subject:"Why does this query take so long\?"

Re: Why does this query take so long?

2009-12-29 Thread Joerg Bruehe

Hi René, everybody!


René 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 response, no?

No chance!
Of course, performance of disk drives varies, but AFAIK typical values
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 bit
more than 8 milliseconds per revolution.
Random access means you have to wait (on average) for half a revolution
(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 response
times if the data is not in RAM.

 
 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.

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

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

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


Regards,
Jörg

-- 
Joerg Bruehe,  MySQL Build Team,  joerg.bru...@sun.com
Sun Microsystems GmbH,   Komturstraße 18a,   D-12099 Berlin
Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels, Wolf Frenkel
Vorsitzender des Aufsichtsrates: Martin Haering Muenchen: HRB161028


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

Re: Why does this query take so long?

2009-12-28 Thread René Fournier

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...@renefournier.com
 


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

RE: Why does this query take so long?

2009-12-28 Thread Gavin Towey

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:m...@renefournier.com]
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

Re: Why does this query take so long?

2009-12-28 Thread René Fournier

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:m...@renefournier.com]
 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

Why does this query take so long?

2009-12-27 Thread René Fournier

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

Re: Why does this query take so long?

2009-12-27 Thread René Fournier

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=arch...@jab.org

Re: Why does this query take so long?

2009-12-27 Thread René Fournier

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=arch...@jab.org

Why does 1st query take so long?

2001-01-16 Thread j.d.stumbles


I've noticed that when developing queries involving complex joins on
tables I sometimes seem to get appallingly long times the first time I run
a query, but the second and subsequent time I run it - even substituting
a different value for a field value I'm matching on - the query runs
quickly.

Does mySQL do some behind-the-scenes reindexing the first time,
resulting in the speed differences I see? If so is there some way I can
force it to re-build indices periodically so I can ensure that queries are
generally fast? Is this the point I should be going out and buying a book
about mySQL? ;-)

Here'a an example query:

SELECT d.ifIndex, d.MAC, a.IPadd, d.nMACs, d.mtime, x.deviceID, x.ifIndex
FROM dot1d as d
LEFT JOIN MAC_connections as x
ON d.MAC = x.MAC
LEFT JOIN IP_MAC as a
ON a.MAC = d.MAC
WHERE x.deviceID=d.deviceID AND x.ifIndex=d.ifIndex AND
d.deviceID= {some value}

My MAC_Connections table is indexed on MAC, and IP_MAC is indexed on MAC.

mySQL Version is 3.22.32


regards,

--
John Stumbles  [EMAIL PROTECTED]
I.T. Services Centre,   University of Reading  http://www.rdg.ac.uk/~visstmbl
+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+
never generalise


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Why does 1st query take so long?

2001-01-16 Thread Milo Stefani

- Original Message - 
From: [EMAIL PROTECTED]
To: "mySQL list" [EMAIL PROTECTED]
Sent: Tuesday, January 16, 2001 3:57 PM
Subject: Why does 1st query take so long?

 I've noticed that when developing queries involving complex joins on
 tables I sometimes seem to get appallingly long times the first time I run
 a query, but the second and subsequent time I run it - even substituting
 a different value for a field value I'm matching on - the query runs
 quickly.

 Does mySQL do some behind-the-scenes reindexing the first time,
 resulting in the speed differences I see? If so is there some way I can
 force it to re-build indices periodically so I can ensure that queries are
 generally fast? Is this the point I should be going out and buying a book
 about mySQL? ;-)

I think its because the Operating System is caching the data the query are run on

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Why does query hang? [was: Why does 1st query take so long?

2001-01-16 Thread John Stumbles


On Tue, 16 Jan 2001, Jeremy D. Zawodny wrote:

 On Tue, Jan 16, 2001 at 08:21:19PM +, John Stumbles wrote:

  I can't readily reproduce the first-time big difference
  scenario.

OK, I can now: I have a table -

MAC_connections:Data records:6689
+--+--+--+-+-+---+
| Field| Type | Null | Key | Default | Extra |
+--+--+--+-+-+---+
| MAC  | char(12) |  | PRI | |   |
| deviceID | int(10) unsigned | YES  | | NULL|   |
| ifIndex  | int(10) unsigned | YES  | | NULL|   |
| nMACs| int(10) unsigned | YES  | | NULL|   |
| mtime| timestamp(14)| YES  | | NULL|   |
+--+--+--+-+-+---+

SELECT * from MAC_connections
or even
SELECT count(*) from MAC_connections

hangs! (it's been sitting there for the last 10 minutes and hasn't
produced anything!)

I guess it was working OK a few hours ago as I was working on a query
involving a LEFT JOIN to this table: suddently it stopped working and
(after hours of hair-tearing and head-banging :-) I eventually got right
back to simple queries like the one above and found where the problem was.

I can get data from other tables OK.

isamchk -e MAC_connections doesn't complain:

Checking ISAM file: MAC_connections
Data records:6689   Deleted blocks:   0
- check file-size
- check delete-chain
- check index reference
- check records and index references

What's going on? Any ideas?

show status shows:
+--++
| Variable_name| Value  |
+--++
| Aborted_clients  | 79 |
| Aborted_connects | 3  |
| Created_tmp_tables   | 289|
| Delayed_insert_threads   | 0  |
| Delayed_writes   | 0  |
| Delayed_errors   | 0  |
| Flush_commands   | 1  |
| Handler_delete   | 56663884   |
| Handler_read_first   | 199|
| Handler_read_key | 645684988  |
| Handler_read_next| 581963979  |
| Handler_read_rnd | 624846288  |
| Handler_update   | 321370818  |
| Handler_write| 287600901  |
| Key_blocks_used  | 7822   |
| Key_read_requests| 1654937380 |
| Key_reads| 157365 |
| Key_write_requests   | 255616015  |
| Key_writes   | 137012352  |
| Max_used_connections | 31 |
| Not_flushed_key_blocks   | 0  |
| Not_flushed_delayed_rows | 0  |
| Open_tables  | 118|
| Open_files   | 101|
| Open_streams | 0  |
| Opened_tables| 1302   |
| Questions| 540752383  |
| Running_threads  | 32 |
| Slow_queries | 229871 |
| Uptime   | 7891238|
+--++

show variables:
++-+
| Variable_name  | Value   |
++-+
| back_log   | 5   |
| connect_timeout| 5   |
| basedir| /opt/local/ |
| datadir| /DATA/mySQLdata/|
| delayed_insert_limit   | 100 |
| delayed_insert_timeout | 300 |
| delayed_queue_size | 1000|
| join_buffer| 131072  |
| flush_time | 0   |
| key_buffer | 8388600 |
| language   | /opt/local/share/mysql/english/ |
| log| OFF |
| log_update | OFF |
| long_query_time| 10  |
| low_priority_updates   | OFF |
| max_allowed_packet | 1048576 |
| max_connections| 100 |
| max_connect_errors | 10  |
| max_delayed_insert_threads | 20  |
| max_join_size  | 4294967295  |
| max_sort_length| 1024|
| max_write_lock_count   | 4294967295  |
| net_buffer_length  | 16384   |
| pid_file   | /usr/local/var/mysqld.pid   |
| port   | 3306|
| protocol_version   | 10  |
| record_buffer  | 131072

Re: Why does query hang? [was: Why does 1st query take so long?

2001-01-16 Thread Drew Wilder-Goodwin


what does a 'show processlist' reveal?

On 17 Jan 2001 00:08:23 +, John Stumbles wrote:
 On Tue, 16 Jan 2001, Jeremy D. Zawodny wrote:
 
  On Tue, Jan 16, 2001 at 08:21:19PM +, John Stumbles wrote:
 
 I can't readily reproduce the first-time big difference
   scenario.
 
 OK, I can now: I have a table -
 
 MAC_connections:  Data records:6689
 +--+--+--+-+-+---+
 | Field| Type | Null | Key | Default | Extra |
 +--+--+--+-+-+---+
 | MAC  | char(12) |  | PRI | |   |
 | deviceID | int(10) unsigned | YES  | | NULL|   |
 | ifIndex  | int(10) unsigned | YES  | | NULL|   |
 | nMACs| int(10) unsigned | YES  | | NULL|   |
 | mtime| timestamp(14)| YES  | | NULL|   |
 +--+--+--+-+-+---+
 
 SELECT * from MAC_connections
   or even
 SELECT count(*) from MAC_connections
 
 hangs! (it's been sitting there for the last 10 minutes and hasn't
 produced anything!)
 
 I guess it was working OK a few hours ago as I was working on a query
 involving a LEFT JOIN to this table: suddently it stopped working and
 (after hours of hair-tearing and head-banging :-) I eventually got right
 back to simple queries like the one above and found where the problem was.
 
 I can get data from other tables OK.
 
 isamchk -e MAC_connections doesn't complain:
 
 Checking ISAM file: MAC_connections
 Data records:6689   Deleted blocks:   0
 - check file-size
 - check delete-chain
 - check index reference
 - check records and index references
 
 What's going on? Any ideas?
 
 show status shows:
 +--++
 | Variable_name| Value  |
 +--++
 | Aborted_clients  | 79 |
 | Aborted_connects | 3  |
 | Created_tmp_tables   | 289|
 | Delayed_insert_threads   | 0  |
 | Delayed_writes   | 0  |
 | Delayed_errors   | 0  |
 | Flush_commands   | 1  |
 | Handler_delete   | 56663884   |
 | Handler_read_first   | 199|
 | Handler_read_key | 645684988  |
 | Handler_read_next| 581963979  |
 | Handler_read_rnd | 624846288  |
 | Handler_update   | 321370818  |
 | Handler_write| 287600901  |
 | Key_blocks_used  | 7822   |
 | Key_read_requests| 1654937380 |
 | Key_reads| 157365 |
 | Key_write_requests   | 255616015  |
 | Key_writes   | 137012352  |
 | Max_used_connections | 31 |
 | Not_flushed_key_blocks   | 0  |
 | Not_flushed_delayed_rows | 0  |
 | Open_tables  | 118|
 | Open_files   | 101|
 | Open_streams | 0  |
 | Opened_tables| 1302   |
 | Questions| 540752383  |
 | Running_threads  | 32 |
 | Slow_queries | 229871 |
 | Uptime   | 7891238|
 +--++
 
 show variables:
 ++-+
 | Variable_name  | Value   |
 ++-+
 | back_log   | 5   |
 | connect_timeout| 5   |
 | basedir| /opt/local/ |
 | datadir| /DATA/mySQLdata/|
 | delayed_insert_limit   | 100 |
 | delayed_insert_timeout | 300 |
 | delayed_queue_size | 1000|
 | join_buffer| 131072  |
 | flush_time | 0   |
 | key_buffer | 8388600 |
 | language   | /opt/local/share/mysql/english/ |
 | log| OFF |
 | log_update | OFF |
 | long_query_time| 10  |
 | low_priority_updates   | OFF |
 | max_allowed_packet | 1048576 |
 | max_connections| 100 |
 | max_connect_errors | 10  |
 | max_delayed_insert_threads | 20  |
 | max_join_size  | 4294967295  |
 | max_sort_length| 1024|
 | max_write_lock_count   | 4294967295  |
 | net_buffer_length  | 16384   |
 | pid_file   |

Re: Why does this query take so long?

Re: Why does this query take so long?

RE: Why does this query take so long?

Re: Why does this query take so long?

Why does this query take so long?

Re: Why does this query take so long?

Re: Why does this query take so long?

Why does 1st query take so long?

Re: Why does 1st query take so long?

Why does query hang? [was: Why does 1st query take so long?

Re: Why does query hang? [was: Why does 1st query take so long?

11 matches

Site Navigation

Mail list logo

Footer information