Re: using between

2005-03-26 Thread Jigal van Hemert 
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

2005-03-25 Thread mos 
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

2005-03-25 Thread 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 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

2005-03-25 Thread Matt Babineau 
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 = =

2004-01-14 Thread Rory McKinley 
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 = =

2004-01-13 Thread Mike Johnson 
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]