Why is my custom MySQL function so much slower than inlining same in query?

Question

I repeatedly use this SELECT query to read unsigned integers representing IPv4 addresses and present them as human readable dotted quad strings.

SELECT CONCAT_WS('.', 
  FLOOR(ip/POW(256,3)),
  MOD(FLOOR(ip/POW(256,2)), 256),
  MOD(FLOOR(ip/256), 256),
  MOD(ip, 256))
FROM ips;

With my test data, this query takes 3.6 seconds to execute.

I thought that creating a custom stored function for the int->string conversion would allow for easier to read queries and allow reuse, so I made this:

CREATE FUNCTION IntToIp(value INT UNSIGNED)
  RETURNS char(15)
  DETERMINISTIC
  RETURN CONCAT_WS(
    '.', 
    FLOOR(value/POW(256,3)),
    MOD(FLOOR(value/POW(256,2)), 256),
    MOD(FLOOR(value/256), 256),
    MOD(value, 256)
  );

With this function my query looks like this:

SELECT IntToIp(ip) FROM ips;

but with my test data, this takes 13.6 seconds to execute.

I would expect this to be slower on first run, as there is an extra level of indirection involved, but nearly 4 times slower seems excessive. Is this much slowness expected?

I'm using out of the box MySQL server 5.1 on Ubuntu 10.10 with no configuration changes.

---

To reproduce my test, create a table and populate with 1,221,201 rows:

CREATE TABLE ips (ip INT UNSIGNED NOT NULL);

DELIMITER //
CREATE PROCEDURE AddIps ()
BEGIN
  DECLARE i INT UNSIGNED DEFAULT POW(2,32)-1;
  WHILE (i>0) DO
    INSERT INTO ips (ip) VALUES (i);
    SET i = IF(i<3517,0,i-3517);
  END WHILE;
END//
DELIMITER ;

CALL AddIps();

Answer 1

Don't reinvent the wheel, use INET_NTOA():

mysql> SELECT INET_NTOA(167773449);
    -> '10.0.5.9'