Fastest way to update info.

[Zie]

I am trying to update an sql database with dynamically updated data from a file.This code will insert new data & update old:

<?phpfunction insert () {	include("sql.php");		$con = mysql_connect($sqlip, $sqluser, $sqlpass);	if (!$con) die('Could not connect: ' . mysql_error());	mysql_select_db($sqldb, $con);		$f = gzopen("data.txt.gz", r);		while (!feof($f)) {		$line = fgets($f);		list($id, $name, $x, $y, $info, $amt, $r) = explode(',', $line);		$name = urldecode($name);			$name = mysql_real_escape_string($name, $con);		$check = mysql_query("SELECT * FROM data_info WHERE id='$id'");		if (mysql_num_rows($check) > 0) {			mysql_query("UPDATE data_info SET name='$name', x='$x', y='$y', info='$info', amt='$amt', r='$r' WHERE id='$id'");		} else {			mysql_query("INSERT INTO data_info SET id='$id', name='$name', x='$x', y='$y', info='$info', amt='$amt', r='$r'");		}	}	mysql_close($con);}?>

And this one just puts the data into the database, no matter if the exact same data exists.

<?phpfunction insert () {	include("sql.php");		$con = mysql_connect($sqlip, $sqluser, $sqlpass);	if (!$con) die('Could not connect: ' . mysql_error());	mysql_select_db($sqldb, $con);		$f = gzopen("data.txt.gz", r);		while (!feof($f)) {		$line = fgets($f);		list($id, $name, $x, $y, $info, $amt, $rank) = explode(',', $line);		$name = urldecode($name);			$name = mysql_real_escape_string($name, $con);		mysql_query("INSERT INTO data_info SET id='$id', name='$name', x='$x', y='$y', info='$info', amt='$amt', rank='$rank'");	}	mysql_close($con);}?>

A little background info: I have a file that updates with certain information every hour(ish), I have a cron exectute this script on the hour for me. Some of the data in the file might change, some might not change, and some only parts of it will change. Each line represents a new set of info, id numbers ($id) will always stay constant with the data they represent. I am trying to make it so that if the $id is already in the table, to update it with that information, and if $id isn't in the table already, insert all of it's information.The 2nd script takes about 30 seconds to go through the whole file and insert everything into the database. The 1st file takes about an hour to go through the whole file and insert/update the records, this is ridiculous. I would like to know what the fast way would be to update information that changes (again, if $id is already in the table), and insert new data (if $id isn't in the table). *At the time of testing, the file (data.txt.gz) was about 5.2mb in size.Edit: I know that I could just truncate the tables, but I don't think that this would be practical for me since the data will be displayed on a website when a user accesses certain parts of the page.Thanks.

[justsomeguy]

The reason it takes so long is because you're sending 2 database queries per line in the file. Database queries are fairly expensive, and you're doing a lot of them (probably tens or hundreds of thousands). You can cut that number in half if you get all of the IDs from the database to start with and store them all in an array, and then look up your existing IDs in the array instead of the database. It will require more memory to run but it will be a lot faster. You'll also save time if you compare the rows in the database with the rows in the file and only update records where the data has changed. You can save even more time if you get the list of IDs in the file first, and use that list to select only the necessary rows from the database instead of all rows.