Re: IP regex

On Mon, Nov 01, 2004 at 09:51:43AM -0500, Michael Stassen wrote:
> Vladimir V. Kolpakov wrote:
> >On Sun, Oct 31, 2004 at 11:31:33PM -0500, Jamie McCarthy wrote:
> >>[email protected] (Vladimir V. Kolpakov) writes:
> >>>On Sun, Oct 31, 2004 at 02:02:26PM -0500, Michael Stassen wrote:
> >>>>Then you store an IP using INET_ATON(IP) and retrieve it with
> >>>>INET_NTOA(ip_column).  That takes 4 bytes instead of 8 to 16

> I don't understand.  Are you saying that an INT column will be more than 4 
> bytes if you take its index into account?  Well, yes, but the same will be 
> true for an indexed VARCHAR(15).  Furthermore, an index on a VARCHAR(15) 
> will be larger than an index on an INT.  Try it.  You'll see that an index 
> on an INT column has key_len=5, and an index on a VARCHAR(15) has 
> key_len=16.  Together, a VARCHAR(15) column + index is at least twice the 
> size of an INT column + index.

Sorry for confusion,
my focus was slightly different: what's retrieval requirements are,
rather then storage requirements. In general they are opposite
in data schema design process.
IP address is not integer by it's nature and is not string also.
It's closer to bits string representation, which can involve
creation of bitmap index (or maked up with SET type),
but quite often can be effectively worked around using fulltex index.

Original author didn't respond on my question about usage
of IP address, so further discussion in that focus becomes meaningless.

As of "integer versus character" question,
I can also show example where storage requirements are equal
for both types,
thus virtually we can call those data whatever way we like.

  open (MYSQL,'|mysql -utest test');
  print MYSQL <<"EoD";
  DROP TABLE IF EXISTS t1;
  CREATE TABLE t1 (i INT(11) UNSIGNED);
  CREATE INDEX i ON t1 (i);
  DROP TABLE IF EXISTS t2;
  CREATE TABLE t2 (c CHAR(4) BINARY);
  CREATE INDEX c ON t2 (c);
  EoD
  for $i (1 .. 255) {
    my $n = (($i * 256 + $i) * 256 + $i) * 256 + $i;
    my $c = pack('CCCC',$i,$i,$i,$i);
    print MYSQL "INSERT INTO t1 VALUES  ($n);";
    print MYSQL "INSERT INTO t2 VALUES ('$c');";
  }
  close MYSQL;
  0;

--w

-- 
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe:    http://lists.mysql.com/[email protected]