MySQL: не следует ли использовать процедуру для поиска соответствия в этом примере намного быстрее?

Этот запрос занимает 0,0002 секунды для выполнения:

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

Очевидно, что locID это ИНДЕКС.

Этот другой запрос использует подпрограмму и занимает 0,0005 секунд для выполнения (он возвращает 30296):

SELECT IPTOLOCID(
'190.131.60.58'
)

Тогда почему этот комбинированный запрос занимает 1,7912 с для выполнения? Кажется, гораздо больше, чем должно быть:

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

На тот случай, если вы найдете это полезным, это таблицы и процедура:

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 ;
Источник

2 ответа

Решение

Я не знаю, почему предыдущий ответ был отвергнут, но он / она был прав. Функция выполняется для каждой строки в вашем location таблица, потому что:

  • нет [NOT] DETERMINISTIC пункт был предоставлен в определении функции, поэтому NOT DETERMINISTIC предполагается
  • LIMIT Предложение применяется в самом конце процесса, когда все строки уже отсканированы, и WHERE условие было проверено для каждого из них

Я не удивлюсь, если оптимизатор решит не использовать индекс, потому что все строки будут в конечном итоге сканироваться. Вы можете проверить это с EXPLAIN

Если вы переопределите свою функцию как DETERMINISTIC также добавьте READS SQL DATA пункт, чтобы избежать каких-либо сюрпризов.

Кстати, эта функция не имеет смысла в отношении ее результата. Это должно быть реализовано в виде представления (тогда проблема не будет применяться).

Источник

Когда вы запускаете запрос, сначала происходит SELECT, а затем ГДЕ приходит и начинает фильтровать SELECT. Из-за этого в вашем запросе каждая строка SELECT запускает вашу функцию IPTOLOCID, а затем выводит ваши данные.

Источник
Другие вопросы по тегам