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.
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];
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.
<cut>
>
> --
> 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
>
>
>
--
Best regards
--
For technical support contracts, visit https://order.mysql.com/?ref=msal
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Alexander Keremidarski <[EMAIL PROTECTED]>
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer
/_/ /_/\_, /___/\___\_\___/ Sofia, Bulgaria
<___/ www.mysql.com M: +359 88 231668
---------------------------------------------------------------------
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
