1

First query, 900ms:

SELECT * FROM geoip.isp_ipv4 
WHERE INET_ATON(?) BETWEEN start_ip AND last_ip LIMIT 1

Second query, 0.5ms:

SELECT * FROM geoip.isp_ipv4 
WHERE INET_ATON(?) BETWEEN start_ip AND last_ip ORDER BY start_ip DESC LIMIT 1

I wonder why there is so much performance difference.

There could be only one result for a given ip, so the sorting doesn't change the returned row in any way...But why is it so faster?

It's the maxmind geoip db (1.59M rows), this is my table structure:

CREATE TABLE `isp_ipv4` (
  `start_ip` int(10) unsigned NOT NULL,
  `last_ip` int(10) unsigned NOT NULL,
  `asn` mediumint(8) unsigned DEFAULT NULL,
  `isp` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `organization` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `osn` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`start_ip`),
  KEY `last_ip` (`last_ip`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

I'm on MySQL 5.7.17, the same happens in both dev (Windows 10) and production (Ubuntu trusty)


The EXPLAINs are identical:

# id, select_type, table, partitions, type, possible_keys, key, key_len, ref, rows, filtered, Extra
'1', 'SIMPLE', 'isp_ipv4', NULL, 'range', 'PRIMARY,last_ip', 'PRIMARY', '4', NULL, '795214', '50.00', 'Using where'
'1', 'SIMPLE', 'isp_ipv4', NULL, 'range', 'PRIMARY,last_ip', 'PRIMARY', '4', NULL, '795214', '50.00', 'Using where'
  • what show EXPLAIN for both query? of course it possible discuss in theory, but much proper - just look - what mysql think about it – a_vlad Feb 2 '17 at 1:33
  • Added, they are identical – the_nuts Feb 2 '17 at 1:47
  • You sure about the plans? I'd expect one of these queries to involve a sort... – mustaccio Feb 2 '17 at 1:50
  • Yes: i.imgur.com/Yt1WzrR.png Anyway the time depends a lot on what ip is asked – the_nuts Feb 2 '17 at 2:00
  • this is what I also think - depending from IP, because it INT - sort could help reduce range – a_vlad Feb 2 '17 at 2:01
0

What was the IP? 1.1.1.1 would do best by starting from the beginning. 255.255.255.255 would work better by starting at the end.

The EXPLAIN does not say whether the "range" was scanned ascending or descending. I don't even think EXPLAIN FORMAT=JSON SELECT ... would point this out.

So... I think your fast one had a high IP address and did start from the end due to the ORDER BY.

More discussion of the intractability of queries like this, plus a solution if the ranges are non-overlapping.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.