Re: Slow query using between
Good morning James -
It looks like you have a multi-column index on the startIpNum and
endIpNum columns, but it's not doing you any good, at least not for
this query.
You don't mention how many rows of data you're searching against,
which would give a better idea as to what might be reasonable to
expect for maximum speed of this query.
Part of the reason is that MySQL (at least prior to 5) will only use
one index per instance of a table per query, and it has chosen the
primary index from that t2 table. Another part of the reason is
that MySQL can't compare a value to a value in an index that is not
at the beginning of said index.
A final reason you may experience a speed problem is that you've put
the number inside quotes, which makes it a string and could well be
forcing MySQL to do a datatype conversion on the data in your tables
prior to comparisons. Try your query without quotes first to see
what kind of difference that makes for you.
Otherwise -
Try joining on the second table again, and comparing against
startIpNum on that table. This makes your query more complex but
might allow MySQL to better use your existing indices. You could
take it a step further and add an index on endIpNum all by itself,
and add the table in a third time, also.
select t1.city, t1.region, t1.latitude, t1.longitude
from hn_iplocation as t1, hn_iprange as t2, hn_iprange as t3
where t1.locid=t2.locid
and t1.locid = t3.locid
and t2.locid = t3.locid
and (2720518136 between t3.startIpNum and t3.endIpNum)
limit 1
or for real fun, add an index to endIpNum and run:
select t1.city, t1.region, t1.latitude, t1.longitude
from hn_iplocation as t1, hn_iprange as t2, hn_iprange as t3, hn_iprange as t4
where t1.locid=t2.locid
and t1.locid = t3.locid
and t1.locid = t4.locid
and t2.locid = t3.locid
and t2.locid = t4.locid
and t3.locid = t4.locid
and (2720518136 = t3.startIpNum)
and (2720518136 = t4.endIpNum)
limit 1
Hope this helps!
Dan
Hello,
I am running Ver 14.7 Distrib 4.1.12, for redhat-linux-gnu
(i386) using readline 4.3 on a Dual Xeon 2.4Ghz RHEL4 box with 4GB
of RAM.
I have a query that takes anywhere from .25 - .85 seconds to
run. Following are the query and the related table structures I have
currently set up and the output from 'explain'. After twiddling with
this query for some time, i cannot seem to get it to run any faster
and was curious if i am over looking something, or am i simply stuck
with a slow query. The problem is that this query runs each time a
user comes to our website, so the slowness tends to add up a little.
Query:
select t1.city, t1.region, t1.latitude, t1.longitude from
hn_iplocation as t1,hn_iprange as t2 where t1.locid=t2.locid and
('2720518136' between t2.startIpNum and t2.endIpNum) limit 1
Explain:
mysql explain select t1.city, t1.region, t1.latitude, t1.longitude
from hn_iplocation as t1,hn_iprange as t2 where t1.locid=t2.locid
and ('2720518136' between t2.startIpNum and t2.endIpNum) limit 1\G
*** 1. row ***
id: 1
select_type: SIMPLE
table: t1
type: ALL
possible_keys: PRIMARY
key: NULL
key_len: NULL
ref: NULL
rows: 20029
Extra:
*** 2. row ***
id: 1
select_type: SIMPLE
table: t2
type: ref
possible_keys: locId
key: locId
key_len: 5
ref: helloneighbour_com_1.t1.locId
rows: 4
Extra: Using where
2 rows in set (0.00 sec)
Table structure T1:
***
mysql describe hn_iplocation;
++--+--+-+-++
| Field | Type | Null | Key | Default | Extra |
++--+--+-+-++
| locId | int(16) unsigned | | PRI | NULL| auto_increment |
| country| char(2) | YES | | NULL
||
| region | char(2) | YES | | NULL
||
| city | varchar(45) | YES | MUL | NULL
||
| postalCode | varchar(7) | YES | MUL | NULL
||
| latitude | float(9,5) | YES | | NULL
||
| longitude | float(9,5) | YES | | NULL
||
++--+--+-+-++
7 rows in set (0.00 sec)
Table structure T2:
***
mysql describe hn_iprange;
++--+--+-+-+---+
| Field | Type | Null | Key | Default | Extra |
++--+--+-+-+---+
| startIpNum | int(10) unsigned | YES | MUL | NULL| |
| endIpNum | int(10) unsigned | YES | | NULL| |
| locId | int(16) unsigned | YES | MUL | NULL| |
Slow query using between
Hello,
I am running Ver 14.7 Distrib 4.1.12, for redhat-linux-gnu (i386)
using readline 4.3 on a Dual Xeon 2.4Ghz RHEL4 box with 4GB of RAM.
I have a query that takes anywhere from .25 - .85 seconds to run.
Following are the query and the related table structures I have
currently set up and the output from 'explain'. After twiddling with
this query for some time, i cannot seem to get it to run any faster
and was curious if i am over looking something, or am i simply stuck
with a slow query. The problem is that this query runs each time a
user comes to our website, so the slowness tends to add up a little.
Query:
select t1.city, t1.region, t1.latitude, t1.longitude from
hn_iplocation as t1,hn_iprange as t2 where t1.locid=t2.locid and
('2720518136' between t2.startIpNum and t2.endIpNum) limit 1
Explain:
mysql explain select t1.city, t1.region, t1.latitude, t1.longitude
from hn_iplocation as t1,hn_iprange as t2 where t1.locid=t2.locid and
('2720518136' between t2.startIpNum and t2.endIpNum) limit 1\G
*** 1. row ***
id: 1
select_type: SIMPLE
table: t1
type: ALL
possible_keys: PRIMARY
key: NULL
key_len: NULL
ref: NULL
rows: 20029
Extra:
*** 2. row ***
id: 1
select_type: SIMPLE
table: t2
type: ref
possible_keys: locId
key: locId
key_len: 5
ref: helloneighbour_com_1.t1.locId
rows: 4
Extra: Using where
2 rows in set (0.00 sec)
Table structure T1:
***
mysql describe hn_iplocation;
++--+--+-+-
++
| Field | Type | Null | Key | Default |
Extra |
++--+--+-+-
++
| locId | int(16) unsigned | | PRI | NULL|
auto_increment |
| country| char(2) | YES | | NULL
||
| region | char(2) | YES | | NULL
||
| city | varchar(45) | YES | MUL | NULL
||
| postalCode | varchar(7) | YES | MUL | NULL
||
| latitude | float(9,5) | YES | | NULL
||
| longitude | float(9,5) | YES | | NULL
||
++--+--+-+-
++
7 rows in set (0.00 sec)
Table structure T2:
***
mysql describe hn_iprange;
++--+--+-+-+---+
| Field | Type | Null | Key | Default | Extra |
++--+--+-+-+---+
| startIpNum | int(10) unsigned | YES | MUL | NULL| |
| endIpNum | int(10) unsigned | YES | | NULL| |
| locId | int(16) unsigned | YES | MUL | NULL| |
++--+--+-+-+---+
3 rows in set (0.00 sec)
Index from T1:
**
mysql show index from hn_iplocation;
+---+++--
+-+---+-+--++--
++-+
| Table | Non_unique | Key_name | Seq_in_index |
Column_name | Collation | Cardinality | Sub_part | Packed | Null |
Index_type | Comment |
+---+++--
+-+---+-+--++--
++-+
| hn_iplocation | 0 | PRIMARY|1 |
locId | A | 20029 | NULL | NULL | |
BTREE | |
| hn_iplocation | 1 | postalcode |1 |
postalCode | A |1820 | NULL | NULL | YES |
BTREE | |
| hn_iplocation | 1 | city |1 |
city| A |1820 | NULL | NULL | YES |
BTREE | |
+---+++--
+-+---+-+--++--
++-+
3 rows in set (0.00 sec)
Index from T2:
**
mysql show index from hn_iprange;
+++--+--+-
+---+-+--++--+
+-+
| Table | Non_unique | Key_name | Seq_in_index | Column_name |
Collation | Cardinality | Sub_part | Packed | Null | Index_type |
Comment |
+++--+--+-
+---+-+--++--+
+-+
| hn_iprange | 1 | locId|1 | locId |
A | 20587 | NULL | NULL | YES | BTREE
| |
| hn_iprange | 1 |
Re: using between
From: Rob Brooks Well, we have this db with various ip address ranges and the country of origin associated with each ... the format is: countryOfOrigin FromIP ToIP --- -- US some lower bound some upper bound Canada some lower bound some upper bound Etc... So the real statement would be: Select countryOfOrigin From IPRangeTable where targetIP between FromIP and ToIP What about creating an index on both FromIP and ToIP and using a query like: SELECT `countryOfOrigin` FROM `IPRangeTable` WHERE `FromIp` = targetIP AND `ToIP` = targetIP; This way MySQL can use the index to search the record and only needs a single read on the table itself to fetch the countryOfOrigin. Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
using between
Hello Is there a way when searching for a range of values for a particular field that mysql would not have to look at the entire table ... I'm guessing with some type of composite key or something? ... e.g. SELECT aField FROM aDatabase WHERE aVariable BETWEEN field1 AND field2; Field1 and field2 are indexed but that doesn't help because you're looking for a range of values instead of a particular value -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: using between
At 01:48 PM 3/25/2005, you wrote: Hello Is there a way when searching for a range of values for a particular field that mysql would not have to look at the entire table ... I'm guessing with some type of composite key or something? ... e.g. SELECT aField FROM aDatabase WHERE aVariable BETWEEN field1 AND field2; Field1 and field2 are indexed but that doesn't help because you're looking for a range of values instead of a particular value Rob, I'm having a hard time wrapping my head around your example.It's like the tail wagging the dog.g Putting variables and columns to your syntax I get: select cust_num from table_invoices where 5 between invoice_amt and tax; I think you meant to say: select aCol from aTable where aCol between val1 and val2; If aCol is indexed, then MySQL will use the index to get a Range on val1 and val2. Just use Explain and you'll see the index that it's using. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: using between
Well, we have this db with various ip address ranges and the country of origin associated with each ... the format is: countryOfOrigin FromIP ToIP --- -- US some lower boundsome upper bound Canada some lower boundsome upper bound Etc... So the real statement would be: Select countryOfOrigin From IPRangeTable where targetIP between FromIP and ToIP Obviously, this looks at every record to see if targetIP is in the range. I'm just trying to think of a better way to do it The ranges are mutually exclusive so once it finds it, that would be it. I guess I could put a 'limit 1' on there to get it to quit once it finds it. Is there something better? -Original Message- From: mos [mailto:[EMAIL PROTECTED] Sent: Friday, March 25, 2005 2:36 PM To: MySQL list Subject: Re: using between At 01:48 PM 3/25/2005, you wrote: Hello Is there a way when searching for a range of values for a particular field that mysql would not have to look at the entire table ... I'm guessing with some type of composite key or something? ... e.g. SELECT aField FROM aDatabase WHERE aVariable BETWEEN field1 AND field2; Field1 and field2 are indexed but that doesn't help because you're looking for a range of values instead of a particular value Rob, I'm having a hard time wrapping my head around your example.It's like the tail wagging the dog.g Putting variables and columns to your syntax I get: select cust_num from table_invoices where 5 between invoice_amt and tax; I think you meant to say: select aCol from aTable where aCol between val1 and val2; If aCol is indexed, then MySQL will use the index to get a Range on val1 and val2. Just use Explain and you'll see the index that it's using. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: using between
Have you considered just doing a parse on the the IP ranges and having 8 columns in your database, then write your query to work inside the 8 columns Cols = from_zone, from_net, from_subnet, from_node, to_zone, to_net, to_subnet, to_node Then just parse the ip you are looking up and write your query that way. MySQL should beable to reduce the amount of rows it needs to look at pretty quickly this way. Matt Babineau Criticalcode w: http://www.criticalcode.com p: 858.733.0160 e: [EMAIL PROTECTED] -Original Message- From: Rob Brooks [mailto:[EMAIL PROTECTED] Sent: Friday, March 25, 2005 1:05 PM To: 'mos'; 'MySQL list' Subject: RE: using between Well, we have this db with various ip address ranges and the country of origin associated with each ... the format is: countryOfOrigin FromIP ToIP --- -- US some lower boundsome upper bound Canada some lower boundsome upper bound Etc... So the real statement would be: Select countryOfOrigin From IPRangeTable where targetIP between FromIP and ToIP Obviously, this looks at every record to see if targetIP is in the range. I'm just trying to think of a better way to do it The ranges are mutually exclusive so once it finds it, that would be it. I guess I could put a 'limit 1' on there to get it to quit once it finds it. Is there something better? -Original Message- From: mos [mailto:[EMAIL PROTECTED] Sent: Friday, March 25, 2005 2:36 PM To: MySQL list Subject: Re: using between At 01:48 PM 3/25/2005, you wrote: Hello Is there a way when searching for a range of values for a particular field that mysql would not have to look at the entire table ... I'm guessing with some type of composite key or something? ... e.g. SELECT aField FROM aDatabase WHERE aVariable BETWEEN field1 AND field2; Field1 and field2 are indexed but that doesn't help because you're looking for a range of values instead of a particular value Rob, I'm having a hard time wrapping my head around your example.It's like the tail wagging the dog.g Putting variables and columns to your syntax I get: select cust_num from table_invoices where 5 between invoice_amt and tax; I think you meant to say: select aCol from aTable where aCol between val1 and val2; If aCol is indexed, then MySQL will use the index to get a Range on val1 and val2. Just use Explain and you'll see the index that it's using. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Using BETWEEN or = =
On 13 Jan 2004 at 10:11, Eve Atley wrote: I am attempting to construct a select statement in which I can find values between two fields: start, and end. I have tried using BETWEEN and comparing with = and =, but neither meet success. Can someone please set me straight? This is meant to be in a PHP page, but I'm assuming the syntax is similar if not the same. I understand there is also a min/max, but I'm not sure it would work in this instance. $sql = SELECT * FROM federal-married WHERE start = .$_POST['salary']. AND end = .$_POST['salary']; Table: federal-married (finds federal tax rate based on marital status) start = min field, ie. 804 end = max field, ie. 2801 $_POST['salary'] = salary of individual posted from a form -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] Hi Eve If I assume that you are looking for a salary that lies between the values stored in the start and end field? In this case your query won't work cos your greater and less thans are a little bit confused. This query (compare with yours above) should work: $sql = SELECT * FROM federal-married WHERE start = .$_POST['salary']. AND end = .$_POST['salary']; Rory McKinley Nebula Solutions +27 82 857 2391 [EMAIL PROTECTED] There are 10 kinds of people in this world, those who understand binary and those who don't (Unknown)
Using BETWEEN or = =
I am attempting to construct a select statement in which I can find values between two fields: start, and end. I have tried using BETWEEN and comparing with = and =, but neither meet success. Can someone please set me straight? This is meant to be in a PHP page, but I'm assuming the syntax is similar if not the same. I understand there is also a min/max, but I'm not sure it would work in this instance. $sql = SELECT * FROM federal-married WHERE start = .$_POST['salary']. AND end = .$_POST['salary']; Table: federal-married (finds federal tax rate based on marital status) start = min field, ie. 804 end = max field, ie. 2801 $_POST['salary'] = salary of individual posted from a form -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Using BETWEEN or = =
From: Eve Atley [mailto:[EMAIL PROTECTED]
I am attempting to construct a select statement in which I
can find values between two fields: start, and end. I have
tried using BETWEEN and comparing with = and =, but
neither meet success. Can someone please set me straight?
This is meant to be in a PHP page, but I'm assuming the
syntax is similar if not the same. I understand there is
also a min/max, but I'm not sure it would work in this
instance.
$sql = SELECT * FROM federal-married WHERE start =
.$_POST['salary']. AND end = .$_POST['salary'];
Table: federal-married (finds federal tax rate based on
marital status)
start = min field, ie. 804
end = max field, ie. 2801
$_POST['salary'] = salary of individual posted from a form
Have you tried this?
$sql =
SELECT *
FROM federal-married
WHERE {$_POST['salary']} BETWEEN start AND end
;
--
Mike Johnson
Web Developer
Smarter Living, Inc.
phone (617) 886-5539
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to select records based on timestamp field (using BETWEEN and AND sql clause
I have a table with one timestamp field named timedate (Unix_timestamp format). I used the follow command but it's not working correctly: mysql select count(*) from tablename where date_format(timedate,%Y-%m-%d) between date_sub(now(), interval 1 DAY) and now(); Empty Set This result is not right since I know there are records in the table that got inserted in the past 24 hours. Does anyone know why? Also it seems unix_timestamp() function only return results in GMT timezone. I don't know how it can return results in US central timezone. Thanks, Hai _ Add photos to your e-mail with MSN 8. Get 2 months FREE*. http://join.msn.com/?page=features/featuredemail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
