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

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
ansible
Forum Newbie
Posts: 3
Joined: Wed Feb 15, 2012 8:08 am

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

Post 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!
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

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

Post by Celauran »

Might I ask why you're doing this? You can just retrieve as UNIX time by using UNIX_TIMESTAMP in your queries.
ansible
Forum Newbie
Posts: 3
Joined: Wed Feb 15, 2012 8:08 am

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

Post 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.
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

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

Post by Celauran »

Code: Select all

SELECT timeStamp, UNIX_TIMESTAMP(timeStamp) AS unix_time ... etc
won't work?
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

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

Post by Weirdan »

Code: Select all

update store_referringurl SET unixTime=unix_timestamp(timeStamp);
ansible
Forum Newbie
Posts: 3
Joined: Wed Feb 15, 2012 8:08 am

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

Post by ansible »

Weirdan wrote:

Code: Select all

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