Hi Slava, On Wed, Sep 09, 2009 at 09:38:03PM +0300, Slava Dubrovskiy wrote:
> Why for store IP it is used CHAR type? > > It's better use INT UNSIGNED and use INET_ATON() and INET_NTOA() for > convert. > http://dev.mysql.com/doc/refman/5.0/en/miscellaneous-functions.html#function_inet-aton This is correct observation. The topic did already get some (although minor) attention in the past and as a result some patches were produced (for releases 0.7.9 and 0.10.x; i can find them again and point them to you); they never managed to get merged into mainstream due to requiring major coding for a clean job and in contrast lack of general interest. My impression is that using INTs instead of CHARs has overall more consistent cons than pros. Pros: it saves space and allows for more efficient range lookups avoiding LIKEs (but, in turn, if one needs that, the *_net primitives are readily available in pmacct). Cons: it doesn't look like there is any support for IPv6, data conversion does not come for free: has performance impact on each INSERT and UPDATE query which need instead to be processed as fast as possible and some SELECTs (ie. SELECT INET_NTOA(ip_src) ...). Two extra considerations: a) if one has a particular eye for this, probably the best choice is to consider using PostgreSQL rather than MySQL as it features the 'inet' (IPv4, IPv6) and 'macaddr' data types: IHMO, they did a true clean job with it; b) the real performance loss heppans when using variable CHAR fields - but this is never the case with pmacct; static length CHAR fields might be slightly less efficient in terms of size than INTs but, you know, overall it has never got that bad to justify going a different way. Indeed i'm open to discussion. Cheers, Paolo _______________________________________________ pmacct-discussion mailing list http://www.pmacct.net/#mailinglists
