Maybe a group project?
Moderator: General Moderators
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...
`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...
- smpdawg
- Forum Contributor
- Posts: 292
- Joined: Thu Jan 27, 2005 3:10 pm
- Location: Houston, TX
- Contact:
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.
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.
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
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
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>";
?>- John Cartwright
- Site Admin
- Posts: 11470
- Joined: Tue Dec 23, 2003 2:10 am
- Location: Toronto
- Contact:
- smpdawg
- Forum Contributor
- Posts: 292
- Joined: Thu Jan 27, 2005 3:10 pm
- Location: Houston, TX
- Contact:
Like this perhaps.
BTW - I can't stand behind this query as I don't have a DB to test against.
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)
Last edited by smpdawg on Wed Mar 02, 2005 7:29 pm, edited 2 times in total.
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
`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
- smpdawg
- Forum Contributor
- Posts: 292
- Joined: Thu Jan 27, 2005 3:10 pm
- Location: Houston, TX
- Contact:
You could add something like this to the WHERE clause
If you have a chance, try the other query and let me know how long it takes.
Code: Select all
Last_Visit BETWEEN 'yesterday 00:00:00' and 'yesterday 23:59:59';