List:General Discussion« Previous MessageNext Message »
From:Dan Nelson Date:March 5 2003 5:12pm
Subject:Re: HAVING vs. WHERE
View as plain text  
In the last episode (Mar 05), Jonathan Arnold said:
> In the MySQL reference, it warns against using HAVING for items that
> "should" be in a WHERE clause. I'm not sure what items "should" be in
> a WHERE clause.

The WHERE clause is used to restrict records, and is also used by the
query optimizer to determine which indexes and tables to use.  HAVING
is a "filter" on the final resultset, and is applied after ORDER BY and
GROUP BY, so mysql cannot use it to optimize the query.

> My exact problem is I want to select some records that have a dotted
> IP address as one of the fields. And I want to filter out the
> multicast addresses, which are the addresses that begin with the
> numbers in the range of 224. thru 239. This does it:
> 
> SELECT * FROM Client WHERE left(inetAdr,instr(inetAdr,"."))< 224
>     OR left(inetAdr,instr(inetAdr,".")) > 239
> 
> and this works as well:
> 
> SELECT *,left(inetAdr,instr(inetAdr,".")) as ia FROM Client HAVING ia < 224
>     OR ia > 239
> 
> and it is a little cleaner, although as I'm going to be doing this in a
> PHP script, cleanliness isn't all that important.
> 
> So I guess I have 2 questions:
> 
> 1] Which should I use?

The first way will be slightly faster, but not by much since you won't
be able to use an index (due to your left/instr functions).

> 2] Is this the easiest way to check for the multicast address?

The fastest way would be to store the IP as an unsigned integer, put an
index on inetAddr, and

SELECT * from Client WHERE inetAddr NOT BETWEEN INET_ATON("224.0.0.0")
AND INET_ATON("239.255.255.255")

Also saves you 12 bytes per record: 16 bytes to store a CHAR(15) vs 4
bytes for an INT.

-- 
	Dan Nelson
	dnelson@stripped
Thread
HAVING vs. WHEREJonathan Arnold5 Mar
  • Re: HAVING vs. WHEREStanimir Dzharkalov5 Mar
  • Re: HAVING vs. WHEREPaul DuBois5 Mar
  • Re: HAVING vs. WHERETore Bostrup5 Mar
  • Re: HAVING vs. WHEREBenjamin Pflugmann5 Mar
  • Re: HAVING vs. WHEREDan Nelson5 Mar