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]
