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]