MySQL Forums
Forum List  »  Performance

Wierd index behaviour
Posted by: Boris Belitsky
Date: September 14, 2005 11:35PM

We are trying to adopt a geolocation base of a very simple structure:

CREATE TABLE `IP2Country` (
`ip_from` int(10) unsigned NOT NULL default '0',
`ip_to` int(10) unsigned NOT NULL default '0',
`country_code_short` char(2) NOT NULL default '',
`country_name` varchar(100) NOT NULL default '',
`state_prov` varchar(50) NOT NULL default '',
`city` varchar(50) NOT NULL default '',
`isp` varchar(60) NOT NULL default '',
PRIMARY KEY (`ip_from`,`ip_to`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

But performace tests show a very strange thing. Speed of this type of query --
"SELECT country_name FROM `IP2Country` WHERE ip_from <= a AND ip_to >= b" -- appears very dependant on values of a and b.
Here are some EXPLAINs to help you understand the idea:

mysql> EXPLAIN SELECT country_name FROM `IP2Country` WHERE ip_from <= 123456789 AND ip_to >= 123456789;
+----+-------------+------------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+-------+---------------+---------+---------+------+------+-------------+
| 1 | SIMPLE | IP2Country | range | PRIMARY | PRIMARY | 4 | NULL | 8 | Using where |
+----+-------------+------------+-------+---------------+---------+---------+------+------+-------------+

mysql> EXPLAIN SELECT country_name FROM `IP2Country` WHERE ip_from <= 234567890 AND ip_to >= 234567890;
+----+-------------+------------+-------+---------------+---------+---------+------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+-------+---------------+---------+---------+------+-------+-------------+
| 1 | SIMPLE | IP2Country | range | PRIMARY | PRIMARY | 4 | NULL | 32625 | Using where |
+----+-------------+------------+-------+---------------+---------+---------+------+-------+-------------+

So the the bigger are a and b values, the more rows are studied and the slower is the query. Moreover the number of studied rows is almost constant for values of 'a' < 201326592 and then it starts growing rapidly and eventually mysql refuses to use index and just checks all records in a table. The value of 'b' doesn't seem to affect things much -- replacing it with a 10 times bigger value doesn't change the number of studied rows.

Can anybody give any ideas on why this thing happens and what could be done to get rid of it? Because that query gets real slow sometimes and we need to run it for each and every visitor, so it eventually causes MySQL to start dropping connections.

Options: ReplyQuote


Subject
Views
Written By
Posted
Wierd index behaviour
1961
September 14, 2005 11:35PM
1269
September 15, 2005 01:55AM
1267
September 15, 2005 02:11AM
1329
September 15, 2005 02:36AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.