Logic for executing 2 selects in MySQL

Question

MySQL

I had a chance to deal with GeoIP. I started with this topic, eventually downloaded and parsed the maxmind database.

Table structure:

CREATE TABLE IF NOT EXISTS `geoip_block` (
`BlockId` int(11) unsigned NOT NULL AUTO_INCREMENT,
`StartNum` int(11) unsigned NOT NULL,
`EndNum` int(10) unsigned NOT NULL,
`LocationId` int(10) unsigned NOT NULL,
PRIMARY KEY (`BlockId`),
KEY `GeoIp_StartNum` (`StartNum`),
KEY `GeoIp_EndNum` (`EndNum`),
KEY `GeoIp_LocationId` (`LocationId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=3620778 ;


Number of records:

mysql> SELECT count(BlockId) FROM geoip_block;
+----------------+
| count(BlockId) |
+----------------+
| 3620777 |
+----------------+
1 row in set (0.00 sec)


I am running 2 requests:

mysql> SELECT SQL_NO_CACHE LocationId FROM geoip_block where StartNum<=1385960996 and EndNum>=1385960996 LIMIT 1;
+------------+
| LocationId |
+------------+
| 161 |
+------------+
1 row in set (5.20 sec)


And

mysql> SELECT SQL_NO_CACHE LocationId FROM (SELECT * FROM geoip_block WHERE StartNum <=1385960996 ORDER BY StartNum DESC LIMIT 1) AS T WHERE EndNum >=1385960996;
+------------+
| LocationId |
+------------+
| 161 |
+------------+
1 row in set (0.0006 sec)


We see that the difference in execution time is huge.

Explain does not give much result:

mysql> EXPLAIN SELECT SQL_NO_CACHE LocationId FROM geoip_block where StartNum<=1385960996 and EndNum>=1385960996 LIMIT 1;
+----+-------------+-------------+-------+-----------------------------+----------------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------+-------+-----------------------------+----------------+---------+------+---------+-------------+
| 1 | SIMPLE | geoip_block | range | GeoIp_StartNum,GeoIp_EndNum | GeoIp_StartNum | 4 | NULL | 1810530 | Using where |
+----+-------------+-------------+-------+-----------------------------+----------------+---------+------+---------+-------------+
1 row in set (0.01 sec)


mysql> EXPLAIN SELECT SQL_NO_CACHE LocationId FROM (SELECT * FROM geoip_block WHERE StartNum <=1385960996 ORDER BY StartNum DESC LIMIT 1) AS T WHERE EndNum >=1385960996;
+----+-------------+-------------+--------+----------------+----------------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------+--------+----------------+----------------+---------+------+---------+-------------+
| 1 | PRIMARY | | system | NULL | NULL | NULL | NULL | 1 | |
| 2 | DERIVED | geoip_block | range | GeoIp_StartNum | GeoIp_StartNum | 4 | NULL | 1810530 | Using where |
+----+-------------+-------------+--------+----------------+----------------+---------+------+---------+-------------+
2 rows in set (0.00 sec)


Question: How does MySQL traverse the table in the case of "<= AND =>" (ps: with BETWEEN the same result)?

Answer 1

Answer: It does not use an index.
If it were to use an index, then the Explain field (for select_type=SIMPLE) would contain the text “Using where; using index;".

Answer 2

And the absence of NO_CACHE in the subquery of the second request does not bother. Actually external NO_CACHE thus has no value.

Answer 3

Also, no_cache is interesting, but the first request might still have disk-only data.
and in the second query it may turn out that the data is already in the cache of the disk subsystem, for example
Try to execute queries on a fresh server in reverse order?