Why does this query take so long?
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
last_insert_id
Hi; mysql select * from products; ++--+--+---++-+---++--++-+--+---+--+---+---++--+--+--+--+-+--+ | ID | SKU | Category | Name | Title | Description | Price | SortFactor | Availability | OutOfStock | ShipFlatFee | ShipPercentPrice | ShipPercentWeight | Associations | TempPrice | LastDatePrice | Weight | Metal| PercentMetal | pic0 | pic1 | sizes | colorsShadesNumbersShort | ++--+--+---++-+---++--++-+--+---+--+---+---++--+--+--+--+-+--+ | 1 | prodSKU1 | prodCat1 | name1 | title1 | desc| 12.34 | 500 |1 | 0 | 10.00 |5 | 2 | | 1 | 2000-01-01| 2.50 | 14k gold | 20 | NULL | NULL | Extra-small | | ++--+--+---++-+---++--++-+--+---+--+---+---++--+--+--+--+-+--+ 1 row in set (0.00 sec) mysql select last_insert_id() from products; +--+ | last_insert_id() | +--+ |0 | +--+ 1 row in set (0.00 sec) mysql Now, I was expecting 1, not 0! What up? TIA, Victor
Re: last_insert_id
2009/12/27 Victor Subervi victorsube...@gmail.com: mysql select * from products; [...] mysql select last_insert_id() from products; [...] Now, I was expecting 1, not 0! What up? [...] LAST_INSERT_ID() (no arguments) returns the first automatically generated value successfully inserted for an AUTO_INCREMENT column as a result of the most recently executed INSERT statement. [...] If no rows were (successfully) inserted, LAST_INSERT_ID() returns 0. http://dev.mysql.com/doc/refman/5.1/en/information-functions.html#function_last-insert-id http://dev.mysql.com/doc/refman/5.1/en/getting-unique-id.html Greetings, Mattia. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: last_insert_id
On Sun, Dec 27, 2009 at 11:27 AM, Mattia Merzi mattia.me...@gmail.comwrote:
2009/12/27 Victor Subervi victorsube...@gmail.com:
mysql select * from products;
[...]
mysql select last_insert_id() from products;
[...]
Now, I was expecting 1, not 0! What up?
[...] LAST_INSERT_ID() (no arguments) returns the first
automatically generated value successfully inserted for
an AUTO_INCREMENT column as a result of the most
recently executed INSERT statement. [...] If no rows
were (successfully) inserted, LAST_INSERT_ID() returns 0.
But it *is* auto incremented!
mysql describe products;
+--+---+--+-+++
| Field|
Type
| Null | Key | Default| Extra |
+--+---+--+-+++
| ID | tinyint(5)
unsigned
| NO | PRI | NULL | auto_increment |
| SKU |
varchar(40)
| NO | UNI | NULL ||
| Category |
varchar(40)
| YES | | NULL ||
| Name |
varchar(50)
| NO | | NULL ||
| Title|
varchar(100)
| NO | | NULL ||
| Description |
mediumtext
| NO | | NULL ||
| Price|
float(8,2)
| YES | | NULL ||
| SortFactor |
int(4)
| YES | | 500||
| Availability |
tinyint(1)
| NO | | 1 ||
| OutOfStock |
tinyint(1)
| NO | | 0 ||
| ShipFlatFee |
float(5,2)
| NO | | 10.00 ||
| ShipPercentPrice | tinyint(2)
unsigned
| NO | | 5 ||
| ShipPercentWeight| tinyint(2)
unsigned
| NO | | 2 ||
| Associations |
varchar(40)
| NO | | NULL ||
| TempPrice|
tinyint(1)
| NO | | 1 ||
| LastDatePrice|
date
| NO | | 2000-01-01 ||
| Weight |
float(7,2)
| NO | | NULL ||
| Metal| enum('14k gold','18k gold','white
gold','silver','tungsten','titanium')
| NO | | NULL ||
| PercentMetal | tinyint(2)
unsigned
| NO | | NULL ||
| pic0 |
mediumblob
| YES | | NULL ||
| pic1 |
mediumblob
| YES | | NULL ||
| sizes|
set('Extra-small','Small','Medium','Large','XLarge','XXLarge','XXXLarge')
| YES | | NULL ||
| colorsShadesNumbersShort |
set('blue:99','gray:465945','purple:50404D','navy-blue:CC7722','fuchsia:FF77FF','aqua:7FFFD4','maroon:B03060','black:FF','yellow:9ACD32')
| YES | | NULL ||
+--+---+--+-+++
23 rows in set (0.00 sec)
mysql
Re: last_insert_id
last_insert_id() returns the last id auto-incremented in *the current
session*. If you disconnect and reconnect, it can not be retrieved.
- michael dykman
On Sun, Dec 27, 2009 at 11:42 AM, Victor Subervi
victorsube...@gmail.com wrote:
On Sun, Dec 27, 2009 at 11:27 AM, Mattia Merzi mattia.me...@gmail.comwrote:
2009/12/27 Victor Subervi victorsube...@gmail.com:
mysql select * from products;
[...]
mysql select last_insert_id() from products;
[...]
Now, I was expecting 1, not 0! What up?
[...] LAST_INSERT_ID() (no arguments) returns the first
automatically generated value successfully inserted for
an AUTO_INCREMENT column as a result of the most
recently executed INSERT statement. [...] If no rows
were (successfully) inserted, LAST_INSERT_ID() returns 0.
But it *is* auto incremented!
mysql describe products;
+--+---+--+-+++
| Field |
Type
| Null | Key | Default | Extra |
+--+---+--+-+++
| ID | tinyint(5)
unsigned
| NO | PRI | NULL | auto_increment |
| SKU |
varchar(40)
| NO | UNI | NULL | |
| Category |
varchar(40)
| YES | | NULL | |
| Name |
varchar(50)
| NO | | NULL | |
| Title |
varchar(100)
| NO | | NULL | |
| Description |
mediumtext
| NO | | NULL | |
| Price |
float(8,2)
| YES | | NULL | |
| SortFactor |
int(4)
| YES | | 500 | |
| Availability |
tinyint(1)
| NO | | 1 | |
| OutOfStock |
tinyint(1)
| NO | | 0 | |
| ShipFlatFee |
float(5,2)
| NO | | 10.00 | |
| ShipPercentPrice | tinyint(2)
unsigned
| NO | | 5 | |
| ShipPercentWeight | tinyint(2)
unsigned
| NO | | 2 | |
| Associations |
varchar(40)
| NO | | NULL | |
| TempPrice |
tinyint(1)
| NO | | 1 | |
| LastDatePrice |
date
| NO | | 2000-01-01 | |
| Weight |
float(7,2)
| NO | | NULL | |
| Metal | enum('14k gold','18k gold','white
gold','silver','tungsten','titanium')
| NO | | NULL | |
| PercentMetal | tinyint(2)
unsigned
| NO | | NULL | |
| pic0 |
mediumblob
| YES | | NULL | |
| pic1 |
mediumblob
| YES | | NULL | |
| sizes |
set('Extra-small','Small','Medium','Large','XLarge','XXLarge','XXXLarge')
| YES | | NULL | |
| colorsShadesNumbersShort |
set('blue:99','gray:465945','purple:50404D','navy-blue:CC7722','fuchsia:FF77FF','aqua:7FFFD4','maroon:B03060','black:FF','yellow:9ACD32')
| YES | | NULL | |
+--+---+--+-+++
23 rows in set (0.00 sec)
mysql
--
- michael dykman
- mdyk...@gmail.com
May you live every day of your life.
Jonathan Swift
Larry's First Law of Language Redesign: Everyone wants the colon.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: last_insert_id
On Sun, Dec 27, 2009 at 12:00 PM, Michael Dykman mdyk...@gmail.com wrote: last_insert_id() returns the last id auto-incremented in *the current session*. If you disconnect and reconnect, it can not be retrieved. Ahah! So how do I retrieve the last id inserted irrespective of connection? TIA, V
Re: last_insert_id
Victor Subervi wrote: On Sun, Dec 27, 2009 at 12:00 PM, Michael Dykman mdyk...@gmail.com wrote: last_insert_id() returns the last id auto-incremented in *the current session*. If you disconnect and reconnect, it can not be retrieved. Ahah! So how do I retrieve the last id inserted irrespective of connection? Would max() work for you? This isn't necessarily foolproof, as it would show the highest ID if you used max(id), for instance - this won't necessarily be what you were expecting, but in most cases will be what you'd imagine it would be. An example of where it wouldn't be: Although ID is auto_increment, you could define a row as, say, '10005583429'. This would be a valid input. Selecting max(id) would return that number. However, auto_increment wouldn't change - it would still be '34' (or whatever) for the next line. Thus, max(id) would be wrong for however long it takes for auto_increment to get to that figure, which could potentially be a long time. Cheers, Gary -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: last_insert_id
At 11:13 AM -0500 12/27/09, you wrote: Hi; mysql select * from products; ++--+--+---++-+---++--++-+--+---+--+---+---++--+--+--+--+-+--+ | ID | SKU | Category | Name | Title | Description | Price | SortFactor | Availability | OutOfStock | ShipFlatFee | ShipPercentPrice | ShipPercentWeight | Associations | TempPrice | LastDatePrice | Weight | Metal| PercentMetal | pic0 | pic1 | sizes | colorsShadesNumbersShort | ++--+--+---++-+---++--++-+--+---+--+---+---++--+--+--+--+-+--+ | 1 | prodSKU1 | prodCat1 | name1 | title1 | desc| 12.34 | 500 |1 | 0 | 10.00 |5 | 2 | | 1 | 2000-01-01| 2.50 | 14k gold | 20 | NULL | NULL | Extra-small | | ++--+--+---++-+---++--++-+--+---+--+---+---++--+--+--+--+-+--+ 1 row in set (0.00 sec) mysql select last_insert_id() from products; +--+ | last_insert_id() | +--+ |0 | +--+ 1 row in set (0.00 sec) mysql Now, I was expecting 1, not 0! What up? TIA, Victor The normal procedure would be to: insert into products values (null, 'prodsku2',...); select last_insert_id(); (assuming ID is your autoincremented field). Do the select last_insert_id() immediately after your insert, and it is guaranteed to give you the ID of the record you just inserted, regardless of what inserts may be happening in other sessions (and if the insert was not successful, it will return 0). If you want to get the highest ID that has been inserted regardless of session or without doing an insert first, you could do a select max(ID). Depending on your overall database design, this may or may not give you what you want. Eg: (1) you can explicitly specify a value for an autoincrement field (eg, insert into products values (1000,'prodsku3'...), which could leave a gap. However, the next autoincrement value in this case would be 1001 and is probably what you want. (2) autoincrement values are not reused after deletion, so if you deleted the record with ID=1000 inserted in (1), the next autoincrement would still be 1001, even if the existing records are IDs 1,2,3. This is usually the desired behavior, but again, may not be what *you* need. I'd recommend spending some time reading the documentation for autoincrement fields and the last_insert_id() function. - sbe - -- ++ | Steve Edberg edb...@edberg-online.com | | Programming/Database/SysAdminhttp://www.edberg-online.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: last_insert_id
On Sun, Dec 27, 2009 at 1:30 PM, Gary Smith li...@l33t-d00d.co.uk wrote: Victor Subervi wrote: On Sun, Dec 27, 2009 at 12:00 PM, Michael Dykman mdyk...@gmail.com wrote: last_insert_id() returns the last id auto-incremented in *the current session*. If you disconnect and reconnect, it can not be retrieved. Ahah! So how do I retrieve the last id inserted irrespective of connection? Would max() work for you? Ahah! No space! Got it. Thanks. V
Re: last_insert_id
Steve Edberg wrote: (2) autoincrement values are not reused after deletion, so if you deleted the record with ID=1000 inserted in (1), the next autoincrement would still be 1001, even if the existing records are IDs 1,2,3. This is usually the desired behavior, but again, may not be what *you* need. Aah... I'd mis-remembered on this. Is there an SQL mode or somesuch which changes this behaviour, or is my mind dribbling out of my ears? Gary -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: last_insert_id
Gary Smith skrev: ... An example of where it wouldn't be: Although ID is auto_increment, you could define a row as, say, '10005583429'. This would be a valid input. Selecting max(id) would return that number. However, auto_increment wouldn't change - it would still be '34' (or whatever) for the next line. Not quite... CREATE TABLE t (id bigint unsigned primary key auto_increment); INSERT INTO t VALUES (10005583429); INSERT INTO t VALUES (null); SELECT * FROM t; +-+ | id | +-+ | 10005583429 | | 10005583430 | +-+ 2 rows in set (0.00 sec) / Carsten -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: last_insert_id
Gary Smith wrote: Steve Edberg wrote: (2) autoincrement values are not reused after deletion, so if you deleted the record with ID=1000 inserted in (1), the next autoincrement would still be 1001, even if the existing records are IDs 1,2,3. This is usually the desired behavior, but again, may not be what *you* need. Aah... I'd mis-remembered on this. Is there an SQL mode or somesuch which changes this behaviour, or is my mind dribbling out of my ears? As far as I'm aware there's no mode to change the default behaviour, but you can always reset the autoincrement value: ALTER TABLE tbl AUTO_INCREMENT = n; Do that, and the next inserted record will have id = n, provided that n is greater than the current maximum value. If, on the other hand, n is lower than or equal to the current maximum value, the next id will be the next value higher than the current maximum. So ALTER TABLE tbl AUTO_INCREMENT = 1; on a non-empty table is functionally equivalent to ALTER TABLE tbl AUTO_INCREMENT = MAX(id) + l (which isn't valid SQL, so don't try it!) If you want to reuse autoincrement values above the current maximum, therefore, you can achieve that in practice by resetting the autoincrement value prior to any insertion. What you can't do, though, is get autoincrement to insert values into the middle of a sequence. So if you have, say, ids 1,2,3,4,5,8,9 and you issue ALTER TABLE tbl AUTO_INCREMENT = 1; or ALTER TABLE tbl AUTO_INCREMENT = 6; then the next inserted id will still be 10, not 6. Mark -- http://mark.goodge.co.uk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Is there a better way than this?
I'm new to MySQL and I'm looking for some guidance. I have a table A, with two columns X and Y with the following data: | X|Y| 1 24 1 25 2 25 2 26 3 27 I want my SQL query to return 2 following this verbose logic: SELECT DISTINCT X FROM A WHERE Y equals 25 and Y also does NOT equal 24. I came up with the following SQL, which gives me my desired result, but is there a better way to do it? Can it be achieved using MINUS or UNION somehow? BTW, I'm using IN here because I intend to replace the single numbers (24 and 25) with arrays that have 0 to N members. SELECT DISTINCT X FROM `A` WHERE X IN ( SELECT X FROM `A` WHERE Y IN (25) ) AND X NOT IN ( SELECT X FROM `A` WHERE Y IN (24) ) Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Is there a better way than this?
On 12/27/2009 06:04 PM, Tim Molter wrote: I'm new to MySQL and I'm looking for some guidance. I have a table A, with two columns X and Y with the following data: | X|Y| 1 24 1 25 2 25 2 26 3 27 I want my SQL query to return 2 following this verbose logic: SELECT DISTINCT X FROM A WHERE Y equals 25 and Y also does NOT equal 24. Since y=25 is associated with both x=1 and x=2, there's no way a simple select can result in 2. Perhaps your assignment calls for the count() of the results? select count(*) from A where y = 25 Good luck, John
Re: Is there a better way than this?
Unless I am missing something, this should work. SELECT DISTINCT X FROM `A` WHERE Y IN (25) AND Y NOT IN (24) Chris W Tim Molter wrote: I'm new to MySQL and I'm looking for some guidance. I have a table A, with two columns X and Y with the following data: | X|Y| 1 24 1 25 2 25 2 26 3 27 I want my SQL query to return 2 following this verbose logic: SELECT DISTINCT X FROM A WHERE Y equals 25 and Y also does NOT equal 24. I came up with the following SQL, which gives me my desired result, but is there a better way to do it? Can it be achieved using MINUS or UNION somehow? BTW, I'm using IN here because I intend to replace the single numbers (24 and 25) with arrays that have 0 to N members. SELECT DISTINCT X FROM `A` WHERE X IN ( SELECT X FROM `A` WHERE Y IN (25) ) AND X NOT IN ( SELECT X FROM `A` WHERE Y IN (24) ) Thanks! -- 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?
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?
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
