MariaDB SQL performance issue with index

0
votes

We have a table for looking up IP Geo-Location on MariaDB mariadb-5.5.37-1.el7_0.x86_64 as below:

CREATE TABLE ip2location_db24 (
id int(11) NOT NULL AUTO_INCREMENT,
ip_from int(10) unsigned DEFAULT NULL,
ip_to int(10) unsigned DEFAULT NULL,
country_code char(2) COLLATE utf8_bin DEFAULT NULL,
country_name varchar(64) COLLATE utf8_bin DEFAULT NULL,
region_name varchar(128) COLLATE utf8_bin DEFAULT NULL,
city_name varchar(128) COLLATE utf8_bin DEFAULT NULL,
latitude double DEFAULT NULL,
longitude double DEFAULT NULL,
zip_code varchar(30) COLLATE utf8_bin DEFAULT NULL,
time_zone varchar(8) COLLATE utf8_bin DEFAULT NULL,
isp varchar(255) COLLATE utf8_bin DEFAULT NULL,
domain varchar(128) COLLATE utf8_bin DEFAULT NULL,
net_speed varchar(8) COLLATE utf8_bin DEFAULT NULL,
idd_code varchar(5) COLLATE utf8_bin DEFAULT NULL,
area_code varchar(30) COLLATE utf8_bin DEFAULT NULL,
weather_station_code varchar(10) COLLATE utf8_bin DEFAULT NULL,
weather_station_name varchar(128) COLLATE utf8_bin DEFAULT NULL,
mcc varchar(256) COLLATE utf8_bin DEFAULT NULL,
mnc varchar(256) COLLATE utf8_bin DEFAULT NULL,
mobile_brand varchar(128) COLLATE utf8_bin DEFAULT NULL,
elevation int(10) DEFAULT NULL,
usage_type varchar(11) COLLATE utf8_bin DEFAULT NULL,
PRIMARY KEY (id),
KEY idx_ip_from (ip_from),
KEY idx_latitude (latitude),
KEY idx_longitude (longitude),
KEY idx_ip_from_to_2 (ip_to,ip_from)
) ENGINE=InnoDB AUTO_INCREMENT=9541211 DEFAULT CHARSET=utf8 COLLATE=utf8_bin

ip_from and ip_to column defines the starting and ending boundaries per geo location.

We have about 10 million records in this table.

When querying geo location for a given IP, we find the server suffers a serious performance issue with the following SQL:

select * from ip2location_db24 where ip_to >=1908980838 and ip_from <=1908980838 limit 1; *************************** 1. row ***************************
id: 5475739
ip_from: 1908932608
ip_to: 1909063679
country_code: CN
country_name: CHINA
region_name: SHANXI
city_name: TAIYUAN
latitude: 37.86944
longitude: 112.56028
zip_code: -
time_zone: +08:00
isp: CHINA UNICOM SHANNXI PROVINCE NETWORK
domain: CHINAUNICOM.COM
net_speed: DSL
idd_code: 86
area_code: 0351
weather_station_code: CHXX0129
weather_station_name: TAIYUAN
mcc: 460
mnc: 01/06
mobile_brand: CHINA UNICOM
elevation: 787
usage_type: ISP/MOB
1 row in set (15.08 sec)

However, when querying using the following equivalent SQL, its very fast.

select * from ip2location_db24 where ip_from <=1908980838 order by ip_from desc limit 1 \G *************************** 1. row ***************************
id: 5475739
ip_from: 1908932608
ip_to: 1909063679
country_code: CN
country_name: CHINA
region_name: SHANXI
city_name: TAIYUAN
latitude: 37.86944
longitude: 112.56028
zip_code: -
time_zone: +08:00
isp: CHINA UNICOM SHANNXI PROVINCE NETWORK
domain: CHINAUNICOM.COM
net_speed: DSL
idd_code: 86
area_code: 0351
weather_station_code: CHXX0129
weather_station_name: TAIYUAN
mcc: 460
mnc: 01/06
mobile_brand: CHINA UNICOM
elevation: 787
usage_type: ISP/MOB
1 row in set (0.00 sec)

The problem is, when we check out the execution plan, both of the queries uses the same range scan on index of ip_from column. But these two SQLs' performance are far from close. Anyone has any idea on reason of this?

To provide more information, we also tested query whose output columns are completely covered by indexes.

MariaDB [ip2location] select ip_from,ip_to from ip2location_db24 where ip_to >=1908980838 and ip_from <=1908980838 limit 1;
+------------+------------+
| ip_from | ip_to |
+------------+------------+
| 1908932608 | 1909063679 |
+------------+------------+
1 row in set (0.01 sec)

Note the above query SQL is lightning fast. But when querying any additional column not covered by indexes, it takes unbelievable long period of time:

> MariaDB [ip2location] select ip_from,ip_to,country_code from ip2location_db24 where ip_to >=1908980838 and ip_from <=1908980838 limit 1;
+------------+------------+--------------+
| ip_from | ip_to | country_code |
+------------+------------+--------------+
| 1908932608 | 1909063679 | CN |
+------------+------------+--------------+
1 row in set (10.15 sec)

2
mysqlsqlquery-optimizationmariadb

2 Answers

0
votes
select * from ip2location_db24 where ip_to >=1908980838 and ip_from <=1908980838 limit 1;

Slow because of comparison two columns.

select * from ip2location_db24 where ip_from <=1908980838 order by ip_from desc limit 1

Fast because of only one indexed column is compared.

-1
votes

Try force index as described in the MariaDB manual section Forcing an index:

select ip_from,ip_to,country_code
from ip2location_db24 force index (idx_ip_from_to_2)
where ip_to >=1908980838 and ip_from <=1908980838
limit 1;