Solved

ip2location causes 2-3 second slow queries

Posted on 2008-06-11
13
893 Views
Last Modified: 2013-12-13
I use an ip2location geolocation database from ip2location.com on a heavily used server.

I enabled the slow query log to track performance and it is taking 2-3 seconds to finish a query.
I ran an explain on a particular query an it shows that is has to scan all 3 million+ rows.
The query appears to not be optimized for speed.
I am using the example sql query as recommended on their website.

SELECT * FROM ip2location WHERE 3413882404 <= ipTO AND 3413882404>=ipFROM

Does anyone know how to speed this up dramatically?

We have a powerful server and even though the queries take a long time to execute, our server resources still appear to be at minimal usage.

Dell Poweredge 2950 rack
64-bit CentOS 5
2 x quad core xeon E5335 @ 2.00GHz
4 x SAS hard drive's - raid 10
4GB ram
CREATE TABLE IF NOT EXISTS `ip2location` (
  `ipFROM` int(10) unsigned zerofill NOT NULL default '0000000000',
  `ipTO` int(10) unsigned zerofill NOT NULL default '0000000000',
  `countrySHORT` char(2) NOT NULL default '',
  `countryLONG` varchar(64) NOT NULL default '',
  `ipREGION` varchar(128) NOT NULL default '',
  `ipCITY` varchar(128) NOT NULL default '',
  PRIMARY KEY  (`ipFROM`,`ipTO`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
 
 
Example record:
 
ipFROM 	     ipTO         countrySHORT  countryLONG     ipREGION        ipCITY
0050331648   0050331903   US            UNITED STATES   MASSACHUSETTS   BEVERLY
 
 
 
 
Here is basically how it is implemented on the site using PHP:
 
<?php
 
function Dot2LongIP ($IPaddr) 
{
	if ($IPaddr == "0.0.0.0") 
	{
		return 0;
	} 
	else 
	{
		$ips = split ("\.", "$IPaddr");
		return ($ips[3] + $ips[2] * 256 + $ips[1] * 256 * 256 + $ips[0] * 256 * 256 * 256);
	}
}
 
//Convert the ip to a number
$Longip = Dot2LongIP('203.123.182.36');
 
// Execute SQL query (recommended query from ip2location.com)
echo "SELECT * FROM ip2location WHERE $Longip <= ipTO AND $Longip>=ipFROM";
 
 
?>

Open in new window

0
Comment
Question by:ray-solomon
  • 7
  • 6
13 Comments
 
LVL 8

Expert Comment

by:shanikawm
ID: 21765856
Didn't you create an index for the table 'ip2location'?
0
 
LVL 10

Author Comment

by:ray-solomon
ID: 21765887
There is a single shared index:

ipFROM, ipTO

BTW, I was going to edit the post.
I have to taken another look at the CPU/Memory/MySQL usage, and it went from about 5% usage to 14% usage in just a couple of hours.
0
 
LVL 8

Expert Comment

by:shanikawm
ID: 21765930
Try this

SELECT * FROM ip2location WHERE ipTO >= 3413882404 AND ipFROM <= 3413882404 LIMIT 1
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 10

Author Comment

by:ray-solomon
ID: 21765955
The LIMIT helped a little and had to scan 2271177 rows instead of 3037152 rows.

But it still took a long time. about 7 seconds
0
 
LVL 10

Author Comment

by:ray-solomon
ID: 21765985
What would happen if I change the storage engine from MyISAM to Memory?

The space used for that table is 125.9 MB
0
 
LVL 8

Accepted Solution

by:
shanikawm earned 500 total points
ID: 21765987
Refer following page. Similar solution is there. But that table has an index for only one column. Not a shared one.

http://forums.mysql.com/read.php?24,72255,72255
0
 
LVL 8

Expert Comment

by:shanikawm
ID: 21766053
0
 
LVL 10

Author Comment

by:ray-solomon
ID: 21766172
I followed the explanation on that page and experimented myself and came up with great results from the mysql forum.
I played around with the indexes and found that one index was needed (ipTO).

The query that ip2location recommended. (DO NOT USE):
SELECT * FROM ip2location WHERE 3413882404 <= ipTO AND 3413882404>=ipFROM

This query will pull a result in about 0.0003 to 1 second (only use ipTO for an index)
SELECT * FROM ip2location WHERE ipTO >= 3413882404 AND ipFROM <= 3413882404 LIMIT 1

Below is the fastest query:

This query will pull a result in about ~ 0.0001 seconds (only use ipTO for an index)
SELECT * FROM ip2location WHERE 3413882404 <= ipTO LIMIT 1

Just FYI, I have query caching enabled, since I cannot turn it off, I used different IP's instead which makes it a different query than what is in the cache.

I am going to restart mysql and watch the server resources. It already jumped to almost 18% before now.

Here are the three corresponding code examples that I used.
 
//echo "SELECT * FROM ip2location WHERE $Longip <= ipTO AND $Longip>=ipFROM";
//echo "SELECT * FROM ip2location WHERE ipTO >= $Longip AND ipFROM <= $Longip LIMIT 1";
//echo "SELECT * FROM ip2location WHERE $Longip <= ipTO LIMIT 1";

Open in new window

0
 
LVL 8

Expert Comment

by:shanikawm
ID: 21766219

OK.

FYI, I think you can use query like follow to avoid the cache,

SELECT SQL_NO_CACHE FROM ip2location WHERE 3413882404 <= ipTO LIMIT 1.
0
 
LVL 10

Author Comment

by:ray-solomon
ID: 21766238
Thanks for that, yes, I used that query, but I had to modify it.
You missed the column names to select from.
:)

I am selecting all columns in my situation. It returns the result in 0.0002 seconds.
SELECT SQL_NO_CACHE * FROM ip2location WHERE 3413882404 <= ipTO LIMIT 1
0
 
LVL 8

Expert Comment

by:shanikawm
ID: 21766251
Yes you are correct. :-). Hope you all problems are solved now.
0
 
LVL 10

Author Comment

by:ray-solomon
ID: 21766260
Yes, Thank you much. I hope this post will be very helpful to others.
There must be a lot of people using ip2location databases.
0
 
LVL 10

Author Closing Comment

by:ray-solomon
ID: 31466429
0

Featured Post

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
TCPDF - Create PDF from Form Values and Link to PDF Download 7 28
Test if unique ID is in log file 5 22
Wordpress Query 5 27
Reference key in foreach loop 4 22
Part of the Global Positioning System A geocode (https://developers.google.com/maps/documentation/geocoding/) is the major subset of a GPS coordinate (http://en.wikipedia.org/wiki/Global_Positioning_System), the other parts being the altitude and t…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

820 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question