List:General Discussion« Previous MessageNext Message »
From:Benjamin Pflugmann Date:March 5 2003 5:01pm
Subject:Re: HAVING vs. WHERE
View as plain text  
On Wed 2003-03-05 at 11:17:37 -0500, jdarnold@stripped wrote:
> 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.

Everything except stuff that only works when it's in the HAVING
clause. The HAVING clause is applied only after all rows matching the
WHERE clause have been fetched, i.e. if you put a condition from the
WHERE clause into the HAVING clause, you take away all possibilities
for optimizing.

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

In this case you won't notice a big difference, because the first
query uses an expression on inetAdr and therefore cannot use indexes
either. Try hard to have a pure column on one side of the operator,
like this:

  SELECT * FROM Client WHERE inetAdr NOT BETWEEN "224." AND "239."

which will happily use an index on inetAdr.

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

My version. ;-)

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

You may want to have a look at the functions INET_NTOA() and
INET_ATON().

HTH,

	Benjamin.

-- 
benjamin-mysql@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