Page 1 of 1

More efficient (faster) way to do this mysql update?

Posted: Wed Feb 15, 2012 8:39 am
by ansible
I have a db of about 500k entries that need to convert a date string (i.e. Apr 11 201 22:37) TO unix time (i.e. 1207971420).

I wrote a script that works completely fine. It's just that it's taking FOREVER! Is there a more efficient way of accomplishing the above? Here's the code:

Code: Select all


while ($id <= 345441)
{
	$query = mysql_query("SELECT * FROM ".$prefix."store_referringurl WHERE id LIKE '$id'");

	while ($row = mysql_fetch_assoc($query) )
	{
		$timeStamp = $row["timeStamp"];
	}

$toUT = strtotime("$timeStamp");

mysql_query("update ".$prefix."store_referringurl SET unixTime='$toUT' where id='$id'")or die(mysql_error());

echo"<BR><b>$id</b> updated $timeStamp to <b>$toUT</b><hr>";

$id++;

}

Thanks for any help!

Re: More efficient (faster) way to do this mysql update?

Posted: Wed Feb 15, 2012 8:49 am
by Celauran
Might I ask why you're doing this? You can just retrieve as UNIX time by using UNIX_TIMESTAMP in your queries.

Re: More efficient (faster) way to do this mysql update?

Posted: Wed Feb 15, 2012 9:09 am
by ansible
Celauran wrote:Might I ask why you're doing this? You can just retrieve as UNIX time by using UNIX_TIMESTAMP in your queries.
I know I can get it from queries, but I need to have the unix time as a separate field for something else I'm doing. Thanks.

Re: More efficient (faster) way to do this mysql update?

Posted: Wed Feb 15, 2012 9:16 am
by Celauran

Code: Select all

SELECT timeStamp, UNIX_TIMESTAMP(timeStamp) AS unix_time ... etc
won't work?

Re: More efficient (faster) way to do this mysql update?

Posted: Wed Feb 15, 2012 10:48 am
by Weirdan

Code: Select all

update store_referringurl SET unixTime=unix_timestamp(timeStamp);

Re: More efficient (faster) way to do this mysql update?

Posted: Wed Feb 15, 2012 2:17 pm
by ansible
Weirdan wrote:

Code: Select all

update store_referringurl SET unixTime=unix_timestamp(timeStamp);
Thanks Weirdan, that is working quite a bit faster!