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