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
[EMAIL PROTECTED]
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
