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


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

	Dan Nelson
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