Edit: the query I show here is not the right one (it does not sort out the right albums). I have no time now to look up the right one but I will post it here shortly.Last weekend I came up with a new approach: try to let the MySQL server come up with the right thumnail for the lastalb view. The definition for "right thumbnail for lastalb view" is in my opinion:
- If there is a thumb defined for that album: that's the right thumb
- If not: the thumb of the picture that was last added to the album is the right thumb.
The code I use to accomplish this is as follows:
case 'lastalb': // Last albums to which uploads
if ($META_ALBUM_SET && $CURRENT_CAT_NAME) {
$album_name = $lang_meta_album_names['lastalb'].' - '. $CURRENT_CAT_NAME;
} else {
$album_name = $lang_meta_album_names['lastalb'];
}
$META_ALBUM_SET = str_replace( "aid", $CONFIG['TABLE_PICTURES'].".aid" , $META_ALBUM_SET );
$query = "SELECT a.*, p.*,a.title AS title, lctime as ctime
FROM {$CONFIG['TABLE_ALBUMS']} AS a
JOIN (
SELECT aid AS laid, MAX(`ctime`) AS lctime, approved
FROM {$CONFIG['TABLE_PICTURES']}
WHERE approved = 'yes' $META_ALBUM_SET
GROUP BY aid
$limit
) as l
ON l.laid = a.aid
LEFT OUTER JOIN {$CONFIG['TABLE_PICTURES']} AS p
ON IF(a.thumb,p.pid = a.thumb,p.ctime = lctime)
ORDER BY lctime DESC
$limit";
$result = cpg_db_query($query);
$count = mysql_num_rows($result);
$rowset = cpg_db_fetch_rowset($result);
mysql_free_result($result);
if ($set_caption) build_caption($rowset,array('ctime'));
$rowset = CPGPluginAPI::filter('thumb_caption_lastalb',$rowset);
return $rowset;
break;
As you see: an inner join on a derived table (subquery in FROM clause) and a conditional outer join. See for derived tables
http://dev.mysql.com/doc/refman/4.1/en/unnamed-views.html The reason I have chosen for a derived table is that I read somewhere this derived table is build once for the total query, not for every record (I am no MySQL guru so I'm not completely sure about that).
One other thing: I do not know where the $count var is for so I don't know if I'm assigning the right value in my code.
So far for the code. The results:
I have stuffed my test gallery with 1631 files and 25 albums. I could not find any more pictures so I started adding duplicates.
The test: under full server stress (apache is processing another heavy PHP function) 3 queries are run in sequence. The (micro)time it takes to execute the query and getting the values in PHP is recorded and after repeating those 3 queries a hundred times a mean query time is calculated.
Query 0 is the original query used for filling the $count value:
SELECT count(cpg148_albums.aid) FROM cpg148_pictures,cpg148_albums WHERE cpg148_pictures.aid = cpg148_albums.aid AND approved = 'YES' GROUP BY cpg148_pictures.aid
Query 1 is the original query used for filling the $result array:
SELECT *,cpg148_albums.title AS title,cpg148_albums.aid AS aid FROM cpg148_pictures,cpg148_albums WHERE cpg148_pictures.aid = cpg148_albums.aid AND approved = 'YES' GROUP BY cpg148_pictures.aid ORDER BY cpg148_pictures.ctime DESC LIMIT 0 ,8
Query 2 is my above mentioned query:
SELECT a.*, p.*,a.title AS title, lctime as ctime FROM cpg148_albums AS a JOIN ( SELECT aid AS laid, MAX(`ctime`) AS lctime, approved FROM cpg148_pictures WHERE approved = 'yes' GROUP BY aid LIMIT 0 ,8 ) as l ON l.laid = a.aid LEFT OUTER JOIN cpg148_pictures AS p ON IF(a.thumb,p.pid = a.thumb,p.ctime = lctime) ORDER BY lctime DESC LIMIT 0 ,8
These are the results under stress:
Query 0 mean execution time: 0.019844 sec
Query 1 mean execution time: 0.074887 sec
Query 2 mean execution time: 0.131075 sec
These are the results under no stress at all:
Query 0 mean execution time: 0.003916 sec
Query 1 mean execution time: 0.026260 sec
Query 2 mean execution time: 0.051477 sec
As you see: my code roughly doubles the time the query takes, but hey: that's an extray query in the derived table <b>and</b> an extra join. And, most important: the right thumbnail
I'm looking forward to seeing results from people that can run that query 2 (the one I came up with) an a really huge gallery. You can use PHPMyAdmin for that, you don't have to mess with CP's code (like I did).
...And I'm also really looking forward to hearing from you what you think about this idea.