Page 2 of 3
Posted: Wed Mar 02, 2005 5:45 pm
by feyd
why are the IP_* fields doubles? ip2long generates a 32-bit integer... that's INT(10) UNSIGNED
country_name may need to be an index as well..
Posted: Wed Mar 02, 2005 6:14 pm
by smpdawg
The samples on MaxMind do show them are INT.
CREATE TABLE csv (
start_ip CHAR(15) NOT NULL,
end_ip CHAR(15) NOT NULL,
start INT UNSIGNED NOT NULL,
end INT UNSIGNED NOT NULL,
cc CHAR(2) NOT NULL,
cn VARCHAR(50) NOT NULL
);
Posted: Wed Mar 02, 2005 6:34 pm
by Todd_Z
CREATE TABLE `IP_Countries` (
`IP_FROM` int(10) unsigned NOT NULL default '0',
`IP_TO` int(10) unsigned NOT NULL default '0',
`COUNTRY_CODE2` char(2) NOT NULL default '',
`COUNTRY_CODE3` char(3) NOT NULL default '',
`COUNTRY_NAME` varchar(50) NOT NULL default '',
KEY `IP_FROM` (`IP_FROM`),
KEY `IP_TO` (`IP_TO`),
KEY `COUNTRY_NAME` (`COUNTRY_NAME`)
) TYPE=MyISAM;
It's still too slow for even 500 entries... If I make this map and it takes 10 minutes to load 50,000 ip addresses... then thats kinda a waste of time and effort. Any optimization ideas?
*Edit* Whats the deal with almost 50% of my entries being unknown...
Posted: Wed Mar 02, 2005 6:40 pm
by smpdawg
Inserting rows is slowing than searching on indexed fields. To add the row MySQL needs to process the data, insert it into the table, build indexes, etc. Once it is in there searches can be quite fast because the indexes are set and no data is written.
When you think about it 50,000 inserts in 10 minutes isn't terrible - that is 83 inserts per second. The reads will be faster than that.
I have done this before and I know it wasn't too slow. I processed over 500,000 IP's with it but I don't recall how long it took.
Posted: Wed Mar 02, 2005 6:57 pm
by Todd_Z
No no, I'm not inserting 50,000 ip addresses, I'm reading the 50,000 ip addresses from one database, then using those ips, i am finding out what country they are from, then finding out the percentage of the total ips are from each country... even 16,000 ips takes a really long time to search for their location in the country db
Posted: Wed Mar 02, 2005 7:00 pm
by smpdawg
Got it. I thought you were saying that the act of inserting the rows was taking that long.
What do the queries look like that are doing these calculations? I'd like to take a look.
Posted: Wed Mar 02, 2005 7:08 pm
by Todd_Z
Working version:
http://www.acdrifter.com/Admin/IPs.php
Thats only 582 IPs though, just for march 1st...
I want to do it for the whole database, which is just shy of 17k
Code: Select all
<?
function utime () {
$time = explode( " ", microtime());
$usec = (double)$timeї0];
$sec = (double)$timeї1];
return $sec + $usec;
}
$start = utime();
$db = mysql_connect("localhost", "*", "*");
mysql_select_db("cinemati_BoxInfo",$db);
$ips = array();
$countries = array();
$sql = "SELECT `IP_Address` FROM `Visitors`";
$res = mysql_query($sql);
while ( $ip = mysql_fetch_object($res) )
$ipsї] = $ip->IP_Address;
echo "Total ips: ".count($ips);
$x = 1;
foreach ( $ips as $ip ) {
$ipLong = ip2long($ip);
$sql = "SELECT `COUNTRY_NAME` FROM `IP_Countries` WHERE `IP_FROM` <= '$ipLong' and `IP_TO` >= '$ipLong'";
$country = mysql_fetch_object(mysql_query($sql));
$country = $country->COUNTRY_NAME;
if ( !$country ) $country = "UNKNOWN ";
echo "<br>$x. $ip: $country";
if ( gettype($countriesї$country]) != "array" )
$countriesї$country] = array();
$countriesї$country]ї] = $ip;
$x++;
}
//echo "<br><br>----------------<br><br>";
//print_r($countries);
echo "<br><br>----------------<br>";
foreach ( $countries as $country => $ipAddrs ) {
echo "<br>$country: ".count($ipAddrs);
foreach ( $ipAddrs as $ipAddr )
echo "<br> $ipAddr";
}
echo "<br><br><b>Page created in ".substr(utime() - $start, 0, 5)." seconds.</b>";
?>
Posted: Wed Mar 02, 2005 7:11 pm
by smpdawg
Why are you storing your IP information in an array rather than doing a join on the two tables?
Posted: Wed Mar 02, 2005 7:18 pm
by John Cartwright
MYSQL is not the fastest database out there. If speed is seriously an issue, maybe mysql isnt the best choice?
Posted: Wed Mar 02, 2005 7:18 pm
by smpdawg
Like this perhaps.
Code: Select all
SELECT COUNTRY_NAME, IP_Address FROM Visitors, IP_Countries WHERE IP_FROM <= INET_ATON(IP_Address) AND IP_TO >= INET_ATON(IP_Address)
BTW - I can't stand behind this query as I don't have a DB to test against.
Posted: Wed Mar 02, 2005 7:25 pm
by Todd_Z
Meh - i dont really know how to use that sql... how can i add a WHERE specification so that i can specify only one day of ips? And what will that return anyways
Posted: Wed Mar 02, 2005 7:29 pm
by smpdawg
I modified it a bit since you saw it. It just returns the country name and ip right now.
Can you give me a mysql structure for you Visitors table?
Posted: Wed Mar 02, 2005 7:30 pm
by Todd_Z
CREATE TABLE `Visitors` (
`IP_Address` varchar(15) NOT NULL default '',
`Days` smallint(5) unsigned NOT NULL default '0',
`Pages` mediumint(8) unsigned NOT NULL default '0',
`Dates` text NOT NULL,
`Last_Visit` timestamp(14) NOT NULL,
`Browser` tinytext NOT NULL,
`ID` mediumint(8) unsigned NOT NULL auto_increment,
PRIMARY KEY (`ID`)
) TYPE=MyISAM PACK_KEYS=0 AUTO_INCREMENT=16563 ;
Sample of a row:
IP - Days - Pages - Dates - Last_Visit - Browser - ID
216.239.39.5 - 4 - 4 - 2.3.05-2.12.05-2.13.05-2.14.05 - 20050214205649 - Mozilla/4.0 (compatible; MSIE 5.01; Windows 98),gz... - 5362
Posted: Wed Mar 02, 2005 7:38 pm
by smpdawg
You could add something like this to the WHERE clause
Code: Select all
Last_Visit BETWEEN 'yesterday 00:00:00' and 'yesterday 23:59:59';
If you have a chance, try the other query and let me know how long it takes.
Posted: Wed Mar 02, 2005 7:40 pm
by Todd_Z