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