blocks.table

CREATE TABLE `blocks` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `begin_range` int(10) unsigned NOT NULL,
  `end_range` int(10) unsigned NOT NULL,
  `_location_id` int(11) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `begin_range` (`begin_range`,`end_range`)
) ENGINE=MyISAM AUTO_INCREMENT=2008839 DEFAULT CHARSET=ascii;

gistfile1.txt

EXPLAIN SELECT locations.latitude, locations.longitude FROM blocks INNER JOIN locations ON blocks._location_id = locations.id WHERE INET_ATON('94.137.106.123') BETWEEN begin_range AND end_range LIMIT 0, 1;

+----+-------------+-----------+--------+---------------+-------------+---------+---------------------------+---------+------------------------------------+
| id | select_type | table     | type   | possible_keys | key         | key_len | ref                       | rows    | Extra                              |
+----+-------------+-----------+--------+---------------+-------------+---------+---------------------------+---------+------------------------------------+
|  1 | SIMPLE      | blocks    | range  | begin_range   | begin_range | 4       | NULL                      | 1095345 | Using index condition; Using where |
|  1 | SIMPLE      | locations | eq_ref | PRIMARY       | PRIMARY     | 4       | geoip.blocks._location_id |       1 | NULL                               |
+----+-------------+-----------+--------+---------------+-------------+---------+---------------------------+---------+------------------------------------+
2 rows in set (0.00 sec)

EXPLAIN EXTENDED SELECT locations.latitude, locations.longitude FROM blocks INNER JOIN locations ON blocks._location_id = locations.id WHERE INET_ATON('94.137.106.123') BETWEEN begin_range AND end_range LIMIT 0, 1;

+----+-------------+-----------+--------+---------------+-------------+---------+---------------------------+---------+----------+------------------------------------+
| id | select_type | table     | type   | possible_keys | key         | key_len | ref                       | rows    | filtered | Extra                              |
+----+-------------+-----------+--------+---------------+-------------+---------+---------------------------+---------+----------+------------------------------------+
|  1 | SIMPLE      | blocks    | range  | begin_range   | begin_range | 4       | NULL                      | 1095345 |   100.00 | Using index condition; Using where |
|  1 | SIMPLE      | locations | eq_ref | PRIMARY       | PRIMARY     | 4       | geoip.blocks._location_id |       1 |   100.00 | NULL                               |
+----+-------------+-----------+--------+---------------+-------------+---------+---------------------------+---------+----------+------------------------------------+

locations.table

CREATE TABLE `locations` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `country` varchar(2) NOT NULL DEFAULT '',
  `region` varchar(255) DEFAULT NULL,
  `city` varchar(255) DEFAULT NULL,
  `postalcode` varchar(255) DEFAULT NULL,
  `latitude` float NOT NULL,
  `longitude` float NOT NULL,
  `metro_code` int(11) DEFAULT NULL,
  `area_code` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=641607 DEFAULT CHARSET=utf8;

my.cnf

# Tried to work with these vars with no affect on performance really
# this is the full my.cnf on a Mac OS X system
# MySQL 5.6 installed using the install binary straight from their page

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 
key_buffer_size = 512M
tmp_table_size = 512M
max_heap_table_size = 32M

slow_query_log = 1
slow_query_log_file = /var/lib/mysql/mysql-slow.log

query

SELECT
   locations.latitude,
   locations.longitude
FROM
   blocks
INNER JOIN
   locations
ON
   blocks._location_id = locations.id
WHERE
   INET_ATON('139.130.4.5') BETWEEN begin_range AND end_range
LIMIT
   0, 1;