MoulinCréatif.com

Lately some of my clients have been running into performance issues with PHPmotion when they have lots of videos (20,000+ for some). One of the main issues (which has also been mentioned on the PHPmotion forum) is with most commented feature in PHPmotion. Below is an explanation of the problem and more interestingly the solution!

The Problem:

The logic behind the whole most commented process is completely wrong. Here’s how it works at the moment.

• User requests most commented videos
• The script gets every single video from the database (every single one!)
• It then loops through every video and does another 3 queries to get the username, number of comments and star rating.
• It then loops through every video again to sort them by most comments.
• It then loops through once more! This time to only return the amount of videos defined by the limit.

To return 12, 20 or even 3 videos it searches through and loops through every video.If you have quite a few videos it can load slowly or worse, a client of mine has over 20,000 videos in the DB and the most commented just kills the page instantly because it uses too much memory.

Say you have 200 videos in total you are running 601 queries (3 per video + 1 to fetch all videos) to return 20 videos! Now depending on the hosting you have this feature could stop working after you’ve only got a few hundred or a few thousand either way all these database queries are totally unnecessary.

The Solution:

How much of a difference will the below changes make? How about reducing all of the above queries down to one single query.

First lets change the stars_array() function to stop it fetching the rating separately (don’t worry these changes won’t break any of the other parts of the site using stars_array()).

In classes/functions.inc.php find the following code:

function stars_array($vid) {

$sql_stars		= "SELECT * FROM videos WHERE indexer = $vid";
$query_stars	= @mysql_query($sql_stars);
$result_stars	= @mysql_fetch_array($query_stars);
$stars 		= $result_stars['updated_rating'];

//default stars

And change it to:

function stars_array($vid, $stars = null) {

if($stars === null) {
$sql_stars		= "SELECT * FROM videos WHERE indexer = $vid";
$query_stars	= @mysql_query($sql_stars);
$result_stars	= @mysql_fetch_array($query_stars);
$stars 		= $result_stars['updated_rating'];
}
//default stars

What we’re doing here is changing the function to only fetch the stars if they aren’t already provided as an argument.

Now that’s optimized let’s get cracking at fixing the problem. In index_ajax.php we’re going to be replacing the code from line 168 to 242 with the following:

if($which_one == 'comments') {

$browse_videos = array();

$sql = "SELECT v.*, COUNT( vc.indexer ) AS comments, u.user_name as uploaded_by

FROM videos v

JOIN videocomments vc ON vc.video_id = v.indexer

JOIN member_profile u ON u.user_id=v.user_id

WHERE approved = 'yes'

AND public_private = 'public'

GROUP BY vc.video_id

ORDER BY comments DESC

LIMIT $limit";

$qry = mysql_query($sql);

while($row = mysql_fetch_assoc($qry)) {

$row['date_uploaded'] = dateTimeDiff($row['date_uploaded']);

$browse_videos[] = array_merge($row, stars_array($row['indexer'], $row['updated_rating']));

}

$see_more_title = $config['most_commented'];

}

And there we go! You’re down from hundreds of queries to one  smart query which does most of the work for us and then PHP simply outputs it for the templates and displays the results.

What’s changed here? Let’s take a look.

The query:

SELECT v.*, COUNT( vc.indexer ) AS comments, u.user_name as uploaded_by
FROM videos v
JOIN videocomments vc ON vc.video_id = v.indexer
JOIN member_profile u ON u.user_id=v.user_id
WHERE approved = 'yes'
AND public_private = 'public'
GROUP BY vc.video_id
ORDER BY comments DESC
LIMIT $limit

This is the biggest change, what I’ve done here is basically merge all the other queries into one optimized one which fetches all the required data at the same time. It also and most importantly sorts the results and fetches the most commented directly instead of every video in the database.

while($row = mysql_fetch_assoc($qry)) {
$row['date_uploaded'] = dateTimeDiff($row['date_uploaded']);
$browse_videos[] = array_merge($row, stars_array($row['indexer'], $row['updated_rating']));
}

Now that we’ve got rid of the other queries the while loop gets simplified quite a bit. All we have to do here is format the date correctly and merge the stars into the video array. Remember the change we did above to the stars_array function well we’re now using that stars_array($row['indexer'], $row['updated_rating']) and avoiding yet another query for the stars info as we’ve already got that from the main query.

Now we apply similar code to seemore.php change the code between if ($which_one == ‘comments’) { .. everything here .. } to the following:

if ($which_one == 'comments') {    

	$pagination = pagination( "SELECT v.indexer, COUNT(c.indexer) as comments FROM videos v JOIN videocomments c ON v.indexer=c.video_id WHERE 1=1 $sql_public_private GROUP BY v.indexer", $limit);

    	$set_limit = $pagination[0]['set_limit'];

    	$total_pages = $pagination[0]['total_pages'];

    	$current_page = $pagination[0]['current_page'];

    	$total_records = $pagination[0]['total_records'];

    	$next_page = $pagination[0]['next_page'];//use in html navigation (src)

    	$prev_page = $pagination[0]['prev_page'];//use in html navigation (src)

    	$nl = $pagination[0]['nl'];//use in html navigation: next>>

    	$pl = $pagination[0]['pl'];//use in html navigation: <<previous

    	$result_featured	= array();    	        

    	$sql = "SELECT v.*, COUNT( vc.indexer ) AS comments, u.user_name as uploaded_by

FROM videos v

JOIN videocomments vc ON vc.video_id = v.indexer

JOIN member_profile u ON u.user_id=v.user_id

WHERE approved = 'yes'

AND public_private = 'public'

GROUP BY vc.video_id

ORDER BY comments DESC

LIMIT $set_limit, $limit";

		$qry = mysql_query($sql);

		while($row = mysql_fetch_assoc($qry)) {

			$row['date_uploaded'] = dateTimeDiff($row['date_uploaded']);

			$result_featured[] = array_merge($row, stars_array($row['indexer'], $row['updated_rating']));

		}

	$see_more_title = $config['most_commented'];

	//PAGINATION PLUS >> start  -- reusable code

	$url = 'videos/load';								//the url to be put in links - EDIT ME

	$additional_url_variable = '/comments/';			//add addtions information that goes in query string here , e.g. '&load=groups&friends=all' - EDIT ME

	@include_once ($include_base . '/includes/pagination.inc.php');

	//PAGINATION PLUS >> end

}

Here we run the same code again basically with the addition of the pagination. I’ve changed the pagination query slightly by removing the ORDER BY as there’s no need to sort the results here only count them. Because of this I’ve also changed the SELECT to only fetch a minimum of data.

That’s about it! Did this help? Did it break something? Let me know below.

PS. You can now sign up for a newsletter. Get PHPmotion tips and advice straight to your front door (well your inbox).

This entry was posted on Friday, June 25th, 2010 at 3:08 am and is filed under PHPmotion. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.