Hello Alexander,
At 11:25 AM 5/16/2002, Alexander Keremidarski wrote:
>Terra wrote:
>>Greetings,
>
><cut>
>
>>mysql> select *,concat_ws('.',oct1,oct2,oct3,oct4)
>ip,concat(oct1,'.',oct2,'.',oct3,'.',oct4) test,count(*) c from ips group by
>oct1,oct2,oct3,oct4 having c>1;
>>+------+------+------+------+------+-----+-------+--------------+----------------+---+
>>| ID | OCT1 | OCT2 | OCT3 | OCT4 | SID | AVAIL | ip | test | c
>|
>>+------+------+------+------+------+-----+-------+--------------+----------------+---+
>>| 3424 | 63 | 151 | 144 | 84 | 0 | 1 | 63.151.144.8 | 63.151.144.84 | 2
>|
>>| 3425 | 63 | 151 | 144 | 85 | 0 | 1 | 63.151.144.8 | 63.151.144.85 | 2
>|
>>| 3426 | 63 | 151 | 144 | 86 | 0 | 1 | 63.151.144.8 | 63.151.144.86 | 2
>|
>>| 3427 | 63 | 151 | 144 | 87 | 0 | 1 | 63.151.144.8 | 63.151.144.87 | 2
>|
>>Notice the difference between the result columns of 'ip' and 'test'...
>
>It is normal to have a difference there. It is not beacuse of how concat() and()
>concat_ws() work, but how GROUP BY works.
Is this 'unpredictable behavior' documented anywhere as I must have missed or glossed
over it?
Is there a technical nutshell guide on explaining the GROUP BY behavior and/or nuances?
Is there an explanation as to _why_ the field display width was restricted to 12
characters in conjunction with GROUP BY?
>In order to get predictable result you need to use:
>
>select concat_ws('.',oct1,oct2,oct3,oct4) as IP, count(*) as c from ips group by IP
>[having c > 1];
Thank you for the tip...
>Your table is:
>
>mysql> desc ips;
>+-------+---------+------+-----+---------+----------------+
>| Field | Type | Null | Key | Default | Extra |
>+-------+---------+------+-----+---------+----------------+
>| ID | int(11) | | PRI | NULL | auto_increment |
>| OCT1 | int(3) | | | 0 | |
>| OCT2 | int(3) | | | 0 | |
>| OCT3 | int(3) | | | 0 | |
>| OCT4 | int(3) | | MUL | 0 | |
>| SID | int(11) | | MUL | 0 | |
>| AVAIL | int(1) | | | 0 | |
>+-------+---------+------+-----+---------+----------------+
>
>This is huge waste of space
>
>To store OCT1..4 you use INT. INT always occupies 4 bytes no matter how it is
>declared (3) is display size only in your case.
>
>AVAIL also uses 4 bytes consider using ENUM() type
>
>Step1. alter all OCT to TINYNT UNSIGNED - 1 byte range 0..255. Exactly what you
>need.
>This way you will save 4*3 = 12 bytes per row
>
>Step2. Depending on your setup you might consider using single column: ip INT
>UNSIGNED - 4 bytes.
>
>Read about INET_NTOA() INET_ATON() functions and bitwise operators.
Agreed on all points above... ;)
1) I have no clue *why* the departed programmer did the IP table this way, because
whatever he was smoking - he sure as heck didn't share with me...
2) I didn't design nor write it - but rather _inherited_ this cruft
3) Sadly, lots of code would need to be updated to handle the change
4) This table is knee deep in legacy
5) Was written with the notion that it's temporary and we'll retrofit/fix it later
(read #3, #4, & #5, -same ol story-)
6) Boss says if it's not broke - don't spend money to fix it ;)
However I will sneak in the 'TINYINT UNSIGNED' change as that will not externally
impact the existing code base... One day, when I don't have a priority list a mile
long, I plan to convert this to INET_ATON type storage as it makes sorting and
calculated subnet retrievals many times easier...
Overall, thanks for taking the time to investigate this...
--
Terra
sysAdmin
FutureQuest, Inc.
http://www.FutureQuest.net
---------------------------------------------------------------------
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
