Maybe a group project?

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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..
User avatar
smpdawg
Forum Contributor
Posts: 292
Joined: Thu Jan 27, 2005 3:10 pm
Location: Houston, TX
Contact:

Post 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
);
User avatar
Todd_Z
Forum Regular
Posts: 708
Joined: Thu Nov 25, 2004 9:53 pm
Location: U Michigan

Post 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...
User avatar
smpdawg
Forum Contributor
Posts: 292
Joined: Thu Jan 27, 2005 3:10 pm
Location: Houston, TX
Contact:

Post 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.
User avatar
Todd_Z
Forum Regular
Posts: 708
Joined: Thu Nov 25, 2004 9:53 pm
Location: U Michigan

Post 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
User avatar
smpdawg
Forum Contributor
Posts: 292
Joined: Thu Jan 27, 2005 3:10 pm
Location: Houston, TX
Contact:

Post 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.
User avatar
Todd_Z
Forum Regular
Posts: 708
Joined: Thu Nov 25, 2004 9:53 pm
Location: U Michigan

Post 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 () &#123;
		$time = explode( " ", microtime());
		$usec = (double)$time&#1111;0];
		$sec = (double)$time&#1111;1];
		return $sec + $usec;
	&#125;
	
	$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&#1111;] = $ip->IP_Address;
		
	echo "Total ips: ".count($ips);
	
	$x = 1;
	foreach ( $ips as $ip ) &#123;
		$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&#1111;$country]) != "array" )
			$countries&#1111;$country] = array();
		$countries&#1111;$country]&#1111;] = $ip;
		$x++;
	&#125;
	
	//echo "<br><br>----------------<br><br>";
	//print_r($countries);
	echo "<br><br>----------------<br>";
	
	foreach ( $countries as $country => $ipAddrs ) &#123;
		echo "<br>$country: ".count($ipAddrs);
		foreach ( $ipAddrs as $ipAddr )
			echo "<br>&nbsp;&nbsp;&nbsp;&nbsp;$ipAddr";
	&#125;
	
	echo "<br><br><b>Page created in ".substr(utime() - $start, 0, 5)." seconds.</b>";

?>
User avatar
smpdawg
Forum Contributor
Posts: 292
Joined: Thu Jan 27, 2005 3:10 pm
Location: Houston, TX
Contact:

Post by smpdawg »

Why are you storing your IP information in an array rather than doing a join on the two tables?
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

MYSQL is not the fastest database out there. If speed is seriously an issue, maybe mysql isnt the best choice?
User avatar
smpdawg
Forum Contributor
Posts: 292
Joined: Thu Jan 27, 2005 3:10 pm
Location: Houston, TX
Contact:

Post 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.
Last edited by smpdawg on Wed Mar 02, 2005 7:29 pm, edited 2 times in total.
User avatar
Todd_Z
Forum Regular
Posts: 708
Joined: Thu Nov 25, 2004 9:53 pm
Location: U Michigan

Post 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
User avatar
smpdawg
Forum Contributor
Posts: 292
Joined: Thu Jan 27, 2005 3:10 pm
Location: Houston, TX
Contact:

Post 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?
User avatar
Todd_Z
Forum Regular
Posts: 708
Joined: Thu Nov 25, 2004 9:53 pm
Location: U Michigan

Post 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
User avatar
smpdawg
Forum Contributor
Posts: 292
Joined: Thu Jan 27, 2005 3:10 pm
Location: Houston, TX
Contact:

Post 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.
User avatar
Todd_Z
Forum Regular
Posts: 708
Joined: Thu Nov 25, 2004 9:53 pm
Location: U Michigan

Post by Todd_Z »

http://www.acdrifter.com/Admin/IPs2.php

It shows the source kinda
Post Reply