I would suggest that you use an INT column type to store the ip address.
This would use a lot less space than a varchar column (when dealing with the
magnitude that you describe)... the smaller the faster... The following
functions make this very easy:
INET_NTOA and INET_ATON
They are described on
http://www.mysql.com/doc/M/i/Miscellaneous_functions.html
Because the entire address is stored in one INT field if you wanted to do
any kind of select based on subnet you could use bitfield operations.
Hope this helps.
ryan
----- Original Message -----
From: "Paul DuBois" <[EMAIL PROTECTED]>
To: "David Lott" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Sent: Wednesday, July 25, 2001 9:00 PM
Subject: Re: Question on design
> At 7:53 PM -0600 7/25/01, David Lott wrote:
> >(please reply directly)
> >
> >
> >I'm new to mysql and I'm working on a design for an application that
> >will require a LOT of lookups on IP addresses. Data set is on the
> >order of millions of addresses. In my reading, I have not
> >encountered a TYPE of ip_address or some such. Did I miss it?
>
> No. There's no such type in MySQL.
>
> >
> >If there is no such type, what would be the best way to setup the
> >addressing for the fastest possible locate?
> >Use varchar (15) and put the whole address in?
> >Use 4 fields A, B, C & D all as tinyint to represent A.B.C.D?
>
> Or convert the address to a 32-bit integer and store it in an INT column.
> If you use separate columns, there are probably network mask operations
and
> such that you'll find more difficult to do than if you store the address
> in a single column. Depends on what kind of things you want to do with
the
> addresses.
>
> >
> >My assumption is that an integer search would be faster than a text
> >search - am I correct?
>
> A 4-byte integer like INT would certainly be faster than a 15-char string.
>
> >What works best? How would you tackle this?
> >
> >
> >
> >Thanks,
> >David Lott
>
>
> --
> Paul DuBois, [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
>
---------------------------------------------------------------------
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
