Should This Update/Script Take This Long?

Databases
1

I have a remote DB that runs 3 innodb tables that track views and embeds by visitors. This table updates a couple million times per day. I created the table to take the stress of my content tables running on myisam. Instead of having those update throughout the day with mysql queries that just +1 a particular column, they are now updating once per day at night. The script looks something like:


//comparing views and views_old column to avoid grabbing every row, only ones that have updated at least once. but is this faster?
//first query is from the remote database with innodb tables

$grabviews = mysql_query("SELECT trailer_id,views FROM stat_views WHERE views!=views_old",$statistics);

while($views = mysql_fetch_array($grabviews)) {
	
	$trailerid = $views['trailer_id'];
	$views = $views['views'];
	//update embeds on trailers
	$updateviews = mysql_query("UPDATE vids SET views=$views WHERE trailer_id=$trailerid LIMIT 1",$update);

}

//now update old_views to new count
$updateviews = mysql_query("UPDATE stat_views SET views_old=views",$statistics);

The rows in stat_views match up to those in vids. There are about 35K rows (of 50K typically) that require updating when this script runs. It can take up to 20 minutes to complete.

Is that normal?

All feedback appreciated.
Cheers
Ryan

2

Yes, that is normal. Since you are doing 35k + 2 queries. This is way way too many queries, you should always try to avoid to run queries inside the processing of another query.

In this case, I believe this query will do what your looking for.


UPDATE
	dbA.stats_views as sv
	INNER JOIN dbB.vids as v
		ON (sv.trailer_id=v.trailer_id)
SET
	v.views=sv.views
	, sv.views_old=sv.views
WHERE
	sv.views<>sv.views_old

Please note that where it says dbA you will put the database name of the statistics database, and similar on dbB you put the database name of the update database. In addition you need to make certain that the mysql user running the query has access and rights to both databases. To make things easier and faster, you could if possible move the InnoDB tables also into the same database as the MyISAM table.

3

Thanks.

Since it’s a remote database for the $statistics (entirely remote server), how do I apply two link resources to this query? $update and $statistics?

Cheers
Ryan

4

If they are physically remote servers, the query wont work.

In that event, what I would do is this:

mysql_query("TRUNCATE TABLE temp_view", $update);

$result = mysql_query("SELECT
			trailer_id
			, views
		       FROM
			stat_views
		       WHERE
			views<>views_old", $statistics);

$num = 0;
$query = array();

while ($buffer = mysql_fetch_assoc($result)) {
	$query[] = "({$buffer['trailer_id']}, {$buffer['views']})";

	if (++$num % 500 == 0) {
		mysql_query("INSERT INTO
				temp_view
				(
				trailer_id
				, views
				)
			      VALUES
				".implode(',', $query).";
			    ", $update);

		$query = array();
		}
	}

if (!empty($query)) {
	mysql_query("INSERT INTO
			temp_view
			(
			trailer_id
			, views
			)
		      VALUES
			".implode(',', $query).";
		    ", $update);
	}

mysql_query("UPDATE
		stat_views
	     SET
		views_old=views
	     WHERE
		views<>views_old
	    ", $statistics);

mysql_query("UPDATE
		temp_view as t
		INNER JOIN vids as v
			USING (trailer_id)
	     SET
		v.views=t.views
	     ", $update);

Now, please note this is written on the fly here, so there might be some issues with the code, but you should be able to fix those.

The principle is that here we mass insert data into a different table, and then update it after using a joined update. In this case we insert on every 500 rows with data, depending on your database server and setup you can possibly even increase that number or if your unlucky you need to decrease it. It all depends how much data it can handle at once in one query.

For the new table, the code to create it is below.


CREATE TABLE temp_view (
 trailer_id INT NOT NULL
 , views INT NOT NULL
 , PRIMARY KEY (trailer_id)
 ) ENGINE=MYISAM
5

Holy crap. Thanks dude. I’m going to give this a full lookover and see how to use it. I’ll report back the results. Much appreciated.

Cheers
Ryan

6

Wow!

I had to edit the code a bit (as i’m following your lesson for three different queries now). I went from 20 minutes, 10 minutes, 20 minutes, combining for 50 minutes, to about 8 seconds, 2 seconds, 8 seconds.

Incredible!

Thanks so much
Ryan