CODEWITHSUNDEEP
mysql
user2893612
user2893612

Reputation: 1

Using indexes in MySQL

Strange thing happens. Can anyone please explain. I have a table.

CREATE TABLE "country_ip" (
  "begin_ip" varchar(15) NOT NULL,
  "end_ip" varchar(15) NOT NULL,
  "begin_ip_long" int(10) unsigned NOT NULL,
  "end_ip_long" int(10) unsigned NOT NULL,
  "id" char(2) NOT NULL,
  "label" varchar(50) NOT NULL,
  KEY "begin_ip_long" ("begin_ip_long","end_ip_long"),
  KEY "end_ip_long" ("end_ip_long")
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

There are about 150 000 records. next queries have huge time difference

0.06329400 | SELECT * FROM `country_ip` WHERE '1405662435' BETWEEN `begin_ip_long` AND `end_ip_long` LIMIT 1
0.06214600 | SELECT * FROM `country_ip` USE INDEX (begin_ip_long, end_ip_long) WHERE 1405662435 BETWEEN `begin_ip_long` AND `end_ip_long` LIMIT 1
0.00008400 | SELECT * FROM `country_ip` USE INDEX (end_ip_long) WHERE 1405662435 BETWEEN `begin_ip_long` AND `end_ip_long` LIMIT 1

Can anyone explain why does it happens? I mean why only USE INDEX (end_ip_long) helps while using USE INDEX (begin_ip_long) or USE INDEX (begin_ip_long, end_ip_long) has no effect. Thanks.

Upvotes: 0

Views: 59

Answers (2)

Thomas W
Thomas W

Reputation: 14164

Since you can get the single-column (END_IP_LONG) index to work fast, why not just index & search on one column?

I'd probably use BEGIN_IP_LONG and search for the first record where specified IP was >= country.BEGIN_IP_LONG, order them by IP descending, limit 1. That should retrieve the "floor" entry, all you need to do then is check the specified IP is not beyond the END_IP_LONG of that country.

This will accomplish the lookup correctly, using only a single-column index. (All of this assumes ranges do not overlap, which I assume is true given your stated structures.)

As others suggested, investigating MySQL's plan (and perhaps rebuilding statistics) would be worthwhile -- but technically, non-overlapping range lookups should technically be possible using an index on only a single bound.

Upvotes: 1

alexius
alexius

Reputation: 2576

Maybe it's because index (end_ip_long) is smaller then (begin_ip_long, end_ip_long) and it fits in memory and one column is selective enough. While bigger index may require to read from disk.

Upvotes: 1

Related Questions