Do VARCHAR data type affect MyQL performances?

vronsky

Hello community,
pls, i'm having a dispute with my dba about this :
In a Mysql 5 medium size database (about 90 tables with any million records) we have now to add a field who store ip address.
Now, this table will not contain more than a hundred records, while it will be inner joined on almost larger tables in some queries.

Me I would like to use a field like CHAR 15 or VARCHAR 15
so I could input from a web form exactly an ip number with all his points (ex 127.0.0.1).
My dba has set a field BIGINT telling that a char or a varchar could drastically reduce the database performances.In this case from a webform an user has to input somethin like 127000000001 .

Could you kindly give me your opinion?

NogDog

What I would recommend is storing it as an integer type as suggested, but using the PHP ip2long() function to store the value and long2ip() to convert it back to a dotted IP string when reading it back.

// create value to be stored in DB:
$ipInteger = sprintf('%u', ip2long($ip_address_input));

This has the added benefit that the database will have no trouble realizing that '120.1.2.3' is the same thing as '120.001.002.003', since both will be converted to the same exact integer value.

Sxooter

vronsky wrote:
Hello community,
pls, i'm having a dispute with my dba about this :
In a Mysql 5 medium size database (about 90 tables with any million records) we have now to add a field who store ip address.
Now, this table will not contain more than a hundred records, while it will be inner joined on almost larger tables in some queries.

Given that you'll only have a few hundred records, I think your DBA is majoring in minors and minoring in majors. I.e. he's worrying about something that is NOT an issue.

tomhath

No way should you try to get users to enter a single bigint, they won't get it right.

Storing it internally as an integer versus a varchar won't make any difference in performance. As long as you only look at it through your web front end you could use the conversion that NogDog suggested, but using bigint data from anywhere else (SQL Queries, report writers, etc) would be a pain in the neck (sort of like your DBA).

That said, you're probably better off using the bigint. Otherwise you're butting heads with someone who thinks he knows it all and will likely look for a way to get back at you if you cross him.

vronsky

Thank you very much, all you has confirmed my opinion about my DBA and helped with useful informations.

Thanks for thr info on function ip2long, I didn't know it...
and in the meanwhile to make my web users and my dba happy I wrote a few lignes of code :

function present_ipaddr($abbbcccddd, $n_netbits=32) {
        $aaabbbcccddd = sprintf("%012s", $abbbcccddd);
        sscanf(substr($aaabbbcccddd,0,3),'%d',$d1);
        sscanf(substr($aaabbbcccddd,3,3),'%d',$d2);
        sscanf(substr($aaabbbcccddd,6,3),'%d',$d3);
        sscanf(substr($aaabbbcccddd,9,3),'%d',$d4);
        $p = sprintf('%d.%d.%d.%d', $d1, $d2, $d3, $d4);
	if ($n_netbits<32) $p .= '/' . $n_netbits;
	return $p;
}
function encode_ipaddr($text) {
        if (!$text) return false;
        if (4!=sscanf($text, '%d.%d.%d.%d', $d1, $d2, $d3, $d4)) {
		return 0;
        }
        return sprintf('%03d%03d%03d%03d', $d1, $d2, $d3, $d4);
}

But like you say, he is a pain in the neck, yes.

Sxooter

vronsky wrote:
But like you say, he is a pain in the neck, yes.

Well, my point really was that he needs to look for the small things that gain you the most. That's a DBA mindset. Not finding new and interesting ways to torture the people who use your database, for a marginal gain in performance. Heck, right now it's theoretical. It might wind up making things slower.

If, after putting normal code into final testing you found that this new function, storing the IP in a text field, caused problems then and only then, solve the performance problem with fancy tweaking. It's as likely you'll introduce new and interesting bugs by storing IPS in some funky way. And IPv6 is on the way...

Of course, I'm a postgresql guy, so my database has two very capable custom types to store IPs, and another one that's a pretty easy add on, so I don't torture my programmers, I just use the right type for what I'm doing, and they store an ip address as an ip address.

vronsky

Thank you Sxooter for your opinion.
I'm really appreciating it.