On Fri, Nov 30, 2012 at 9:45 AM, Ray Soucy <rps@maine.edu> wrote:
I'll see your disagree and raise you another ;-)
I would say you almost never want to store addresses as character data unless the only thing you're using them for is logging (even then it's questionable). I run into people who do this all the time and it's a nightmare.
It's easy to store a v6 address as a string, but when you want to select a range of IPv6 addresses from a database, not having them represented as integers means you can't do efficient numerical comparisons in your SQL statements, it also makes indexing your table slower; to put it simply, it doesn't scale well.
Hi Ray, If you've stored them in the string format I suggested, the string comparison *is* an efficient numerical comparison. On a CISC processor it may even be implemented with a single instruction byte string comparison. Go test. You may be surprised at the results. The one useful function you can't do directly from a string format is apply an AND mask (netmask). More often than not this is irrelevant: you don't want to load the data and then apply the mask, you want the mask to constrain the data which you load from the database. You'd need the database software to understand the address type and index it with a radix tree, something it can do with neither a string format nor your split 64-bit format. In either case you substitute query by range for query by netmask. WHERE IP>='A' AND IP<='B' WHERE (IPHigh>AHigh AND IPHigh<BHigh) OR (IPHigh=AHigh AND IPHigh!=BHigh IPLow>=ALow) OR (IPHigh!=AHigh AND IPHigh=BHigh AND IPLow<=BLow) OR (IPHigh=AHigh AND IPHigh=BHigh AND IPLow>=ALow AND IPLow<=BLow) Which version looks more efficient to you? Regards, Bill Herrin -- William D. Herrin ................ herrin@dirtside.com bill@herrin.us 3005 Crane Dr. ...................... Web: <http://bill.herrin.us/> Falls Church, VA 22042-3004