title: Why is my custom MySQL function so much slower than inlining same in query?
body: <p>I repeatedly use this <code>SELECT</code> query to read unsigned integers representing IPv4 addresses and present them as human readable dotted quad strings.</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>With my test data, this query takes <strong>3.6 seconds</strong> to execute.</p>
  
  <p>I thought that <a href="http://dev.mysql.com/doc/refman/5.1/en/create-procedure.html" rel="noreferrer">creating a custom stored function</a> for the int->string conversion would allow for easier to read queries and allow reuse, so I made this:</p>
  
  <pre><code>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)
    );
  </code></pre>
  
  <p>With this function my query looks like this:</p>
  
  <pre><code>SELECT IntToIp(ip) FROM ips;
  </code></pre>
  
  <p>but with my test data, this takes <strong>13.6 seconds</strong> to execute.</p>
  
  <p>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. <strong>Is this much slowness expected?</strong></p>
  
  <p>I'm using out of the box MySQL server 5.1 on Ubuntu 10.10 with no configuration changes.</p>
  
  <hr>
  
  <p>To reproduce my test, create a table and populate with 1,221,201 rows:</p>
  
  <pre><code>CREATE TABLE ips (ip INT UNSIGNED NOT NULL);
  
  DELIMITER //
  CREATE PROCEDURE AddIps ()
  BEGIN
    DECLARE i INT UNSIGNED DEFAULT POW(2,32)-1;
    WHILE (i&gt;0) DO
      INSERT INTO ips (ip) VALUES (i);
      SET i = IF(i&lt;3517,0,i-3517);
    END WHILE;
  END//
  DELIMITER ;
  
  CALL AddIps();
  </code></pre>