Interesting, I had never noticed that. I’ll try to understand why is that, and add the limit to thd query. If you got some spare time, could you take a look at the other queries?
Thanks
Camu
I think it’s related to the number of results that can be traversed in the index – if it’s more than ~5% then MySQL just does a full table scan. When you specify the LIMIT it reduces the results and uses the index… at least I think 🙂
There are a few other queries that could benefit from additional indexes (there are queries that use all of the columns in their WHERE):
wp_slim_browsers:
KEY index_all (browser,version,platform,css_version,type)
wp_slim_browsers:
KEY index_all (content_type,category,author)
wp_slim_screenres:
KEY index_all (resolution,colordepth,antialias)
Pretty busy at the moment, but I will definitely let you know if I see any other potential performance enhancements.
Yeah, indexes on those tables can be activated by going to Settings > SlimStat > Maintenance. I’ll review the indexes to make sure I’m creating all of them.
I just run your explain query on a different test environment, and even the one without the limit used the index:
select_type: SIMPLE
table: wp_slim_countries
type: range
possible_keys: ip_from_idx
keys: ip_from_idx
key_len: 5
rows: 26264
extra: Using where
So I guess it depends on the version of MySQL you’re using. I tried both with MyISAM and InnoDB, and the index was used both times (with no limit in the query). I’m running MySQL 5.5 here, what about you?
I’m running MySQL 5.5.24 on CentOS 5.8 – definitely weird since that table is a fixed number of records (I’m assuming?). This is what I get for the two queries:
mysql> explain SELECT country_code FROM wp_slim_countries WHERE ip_from <= 1113230773 AND ip_to >= 1113230773;
+----+-------------+-------------------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------------+------+---------------+------+---------+------+--------+-------------+
| 1 | SIMPLE | wp_slim_countries | ALL | ip_from_idx | NULL | NULL | NULL | 173774 | Using where |
+----+-------------+-------------------+------+---------------+------+---------+------+--------+-------------+
1 row in set (0.00 sec)
mysql> explain SELECT country_code FROM wp_slim_countries WHERE ip_from <= 1113230773 AND ip_to >= 1113230773 limit 1;
+----+-------------+-------------------+-------+---------------+-------------+---------+------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------------+-------+---------------+-------------+---------+------+-------+-------------+
| 1 | SIMPLE | wp_slim_countries | range | ip_from_idx | ip_from_idx | 5 | NULL | 48078 | Using where |
+----+-------------+-------------------+-------+---------------+-------------+---------+------+-------+-------------+
1 row in set (0.00 sec)
And just in case, here is the table definition:
CREATE TABLE wp_slim_countries (
ip_from int(10) unsigned DEFAULT '0',
ip_to int(10) unsigned DEFAULT '0',
country_code char(2) DEFAULT '',
KEY ip_from_idx (ip_from,ip_to)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Weird, really. I’m under Windows, that’s the only difference. Anyway, I’ll add the limit and the other indexes, it doesn’t hurt 😉
Camu
