HAVING vs. WHERE
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. 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? 2] Is this the easiest way to check for the multicast address? -- Jonathan Arnold (mailto:[EMAIL PROTECTED]) Amazing Developments http://www.buddydog.org It ain't what you don't know that gets you into trouble. It's what you know for sure that just ain't so. Mark Twain - 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
Re: HAVING vs. WHERE
go to
http://www.mysql.com/doc/en/Miscellaneous_functions.html
and take a look at
inet_ntoa and inet_aton
you may store IP addresses as integers and the query will be
SELECT inet_aton(inetAdr), * FROM Client WHERE inetAdr
inet_aton('240.0.0.0') OR inetAdrinet_aton('239.255.255.255')
the inet_aton will return the IP dotted.
As well, you do not need to use HAVING here.
Hope this helps.
==
Stanimir Dzharkalov
Developer
Internet Division
MobilTel EAD
email: [EMAIL PROTECTED]
==
- Original Message -
From: Jonathan Arnold [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Wednesday, March 05, 2003 6:17 PM
Subject: HAVING vs. WHERE
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.
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?
2] Is this the easiest way to check for the multicast address?
--
Jonathan Arnold (mailto:[EMAIL PROTECTED])
Amazing Developments http://www.buddydog.org
It ain't what you don't know that gets you into trouble.
It's what you know for sure that just ain't so. Mark Twain
-
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
-
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
Re: HAVING vs. WHERE
At 11:17 -0500 3/5/03, Jonathan Arnold 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. If a test can be in either clause, put it in the WHERE clause. The principle is that the sooner you can eliminate rows from consideration for further processing, the better. WHERE is evaluated to determine which rows to select, HAVING is applied to the selected rows after that. This mostly makes a difference when using HAVING with GROUP BY. If you can use WHERE to prevent certain rows even from entering into the grouping calculations, it saves work compared to eliminating them later in the HAVING. 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? 2] Is this the easiest way to check for the multicast address? -- Jonathan Arnold (mailto:[EMAIL PROTECTED]) Amazing Developments http://www.buddydog.org It ain't what you don't know that gets you into trouble. It's what you know for sure that just ain't so. Mark Twain - 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
Re: HAVING vs. WHERE
HAVING is for qualifying result rows based on the value of aggregate functions, WHERE is for qualifying result rows based on individual (column) values. So in you case you should use WHERE. Although useful in the right situation, HAVING is used much less than a WHERE clause. One example of how to use HAVING would be to show values that have duplicate entries in the database: SELECT FirstName, LastName, Count(*) FROM MyTable GROUP BY FirstName, LastName HAVING Count(*) 1 HTH, Tore. - Original Message - From: Jonathan Arnold [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, March 05, 2003 11:17 AM Subject: HAVING vs. WHERE 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. 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? 2] Is this the easiest way to check for the multicast address? -- Jonathan Arnold (mailto:[EMAIL PROTECTED]) Amazing Developments http://www.buddydog.org It ain't what you don't know that gets you into trouble. It's what you know for sure that just ain't so. Mark Twain - 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 - 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
Re: HAVING vs. WHERE
On Wed 2003-03-05 at 11:17:37 -0500, [EMAIL PROTECTED] 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. -- [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
Re: HAVING vs. WHERE
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
