MySQL: Shouldnt using a routine to find a match be much faster in this example?

Question

This query takes 0.0002 secs to execute:

SELECT country,city
FROM location
WHERE locID = 30296
LIMIT 1

locID is obviously an INDEX.

This other query uses a routine, and takes 0.0005 secs to execute (it returns 30296):

SELECT IPTOLOCID(
'190.131.60.58'
)

Then, why does this combined query take 1.7912 secs to execute? Seems way more than it should be:

SELECT country, city
FROM location
WHERE locID = IPTOLOCID('190.131.60.58')
LIMIT 1

Just in case you find this useful, these are the tables and the routine:

CREATE TABLE  `blocks` (
  `startIPNum` int(10) unsigned NOT NULL,
  `endIPNum` int(10) unsigned NOT NULL,
  `locID` int(10) unsigned NOT NULL,
  PRIMARY KEY  (`startIPNum`,`endIPNum`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 PACK_KEYS=1 DELAY_KEY_WRITE=1;

CREATE TABLE  `location` (
  `locID` int(10) unsigned NOT NULL,
  `country` char(2) default NULL,
  `region` char(2) default NULL,
  `city` varchar(45) default NULL,
  `postalCode` char(7) default NULL,
  `latitude` double default NULL,
  `longitude` double default NULL,
  `dmaCode` char(3) default NULL,
  `areaCode` char(3) default NULL,
  PRIMARY KEY  (`locID`),
  KEY `Index_Country` (`country`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 ROW_FORMAT=FIXED;

DELIMITER $$

DROP FUNCTION IF EXISTS `IPTOLOCID` $$
CREATE FUNCTION `IPTOLOCID`( ip VARCHAR(15)) RETURNS int(10) unsigned
BEGIN
  DECLARE ipn INTEGER UNSIGNED;
  DECLARE locID_var INTEGER;
  IF ip LIKE '192.168.%' OR ip LIKE '10.%' THEN
    RETURN 0;
  END IF;
  SET ipn = INET_ATON(ip);
  SELECT locID INTO locID_var
    FROM `blocks`
    INNER JOIN
      (SELECT MAX(startIPNum) AS start
       FROM `blocks`
       WHERE startIPNum <= ipn) AS s
    ON (startIPNum = s.start)
    WHERE endIPNum >= ipn;
  RETURN locID_var;
END $$

DELIMITER ;

Answer 1

I don't know why the previous answer got downvoted, but s/he had it right. The function is executed for every single row in your location table because:

• no [NOT] DETERMINISTIC clause was provided in the function definition, so NOT DETERMINISTIC is assumed
• the LIMIT clause is applied at the very end of the process, when all rows have already been scanned, and the WHERE condition has been checked for each of them

I wouldn't be surprised if the optimiser decided not to use the index either because all rows will eventually be scanned. You can check this with an EXPLAIN

If you redefine your function as DETERMINISTIC, also add the READS SQL DATA clause to avoid any surprise.

By the way, this function is a non-sense, with regards to its result. This should be implemented as a view instead (then the problem wouldn't apply).

Answer 2

When you run a query, SELECT happens first, and then WHERE comes in after and starts to filter the SELECT. Because of this, in your query, every single SELECT row runs your IPTOLOCID function, and then outputs your data.