MySQL > Range checked for each record

Anything I can do to get better performance?  Both tables are MyISAM.
EXPLAIN SELECT
    mmc.country_code
FROM
    server_logs.page_views  pv
    INNER JOIN network_tools.maxmind_geoip_country mmc ON (pv.remote_host BETWEEN mmc.ip_start AND mmc.ip_end)
WHERE
    pv.country_code=''

Open in new window

+----+-------------+-------+------+----------------------------------+------------------+---------+-------+-------+------------------------------------------------+
| id | select_type | table | type | possible_keys                    | key              | key_len | ref   | rows  | Extra                                          |
+----+-------------+-------+------+----------------------------------+------------------+---------+-------+-------+------------------------------------------------+
|  1 | SIMPLE      | pv    | ref  | IDX_remote_host,IDX_country_code | IDX_country_code | 1       | const |   511 | Using where                                    |
|  1 | SIMPLE      | mmc   | ALL  | PRIMARY                          | NULL             | NULL    | NULL  | 81216 | Range checked for each record (index map: 0x1) |
+----+-------------+-------+------+----------------------------------+------------------+---------+-------+-------+------------------------------------------------+
2 rows in set (0.00 sec)

Open in new window

Geoff MillikanAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dave BaldwinFixer of ProblemsCommented:
I doubt it since you are doing a calculation on every one of the 81,216 rows to find out if it is in range.
0
Geoff MillikanAuthor Commented:
Dang.  Was afraid you'd say that.  Below article suggests using MySQL geospacial functions for looking up IP addresses but seems like an awful lot of developer/DBA time.

http://blog.jcole.us/2007/11/24/on-efficiently-geo-referencing-ips-with-maxmind-geoip-and-mysql-gis/
0
Geoff MillikanAuthor Commented:
Below query is running at 40 ms/row.  It's killing us.
UPDATE
    server_logs.page_views  pv
    INNER JOIN network_tools.maxmind_geoip_country mmc ON (pv.remote_host BETWEEN mmc.ip_start AND mmc.ip_end)
SET
   pv.country_code=mmc.country_code
WHERE
   pv.country_code=''

Open in new window

0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Tomas Helgi JohannssonCommented:
Hi!

If you don't have these indexes I suggest you try them.

create index iprange_ix on maxmind_geoip_country (ip_start asc, ip_end asc, country_code asc) using BTREE;

create index country_ip_ix on page_views  ( country_code asc, remote_host asc) using BTREE;

Never use only one column in an index. Instead combine the columns that you use in your queries where,group by and order by clauses.

Regards,
    Tomas Helgi
0
Geoff MillikanAuthor Commented:
Table maxmind_geoip_country: Since ip_start & ip_end is primary key here seems like indexing it again isn't going to help?  Can you explain why you think it will?

Table page_views: I think the index used in the JOIN and the index used to satisfy the WHERE condition can be separate indexes.  And since remote_host is already in primary key (a clustered index so this column should be available, right?) and since country_code is already indexed in it's own index, can you explain why adding another index will help?

I suppose I could covert the 81,216 row maxmind_geoip_country table to MEMORY storage type which should lower access time?
0
Geoff MillikanAuthor Commented:
Changing the 81,216 row maxmind_geoip_country table to MEMORY storage type increased performance 10x.  Was taking 40 ms/row, now it's 4 ms/row.
ALTER TABLE network_tools.maxmind_geoip_country ENGINE = memory; 

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Tomas Helgi JohannssonCommented:
Hi!

Having these 2 indexes on your tables you are making this query a index only query which is many times faster especially when tables are large.
It's a common mistake to have just one column in an index when you are using more columns of the same table in a where clause.
As I said you will benefit and gain more performance if you have all your where clause columns in an index. That is a proven fact. :)

It's is obvious that having all the data in memory speeds things up but I strongly suggest you try these indexes. :)

Regards,
    Tomas Helgi
0
Geoff MillikanAuthor Commented:
Tomas,

I think you were having me put an index on columns already in an index (the PRIMARY index) so that's not going to help but I'm awarding you points because our conversation made me remember I could just put the whole table in RAM.  I forgot about that!

Thanks for interacting on this!

http://www.t1shopper.com/
0
Geoff MillikanAuthor Commented:
Thanks so much!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.