I did some first modifications on cpg 1.3.1 to deliver quick frontpage results on lastup/random/lastcom.
Our frontpage for our gallery got pretty slow starting with 5k pics with 2k user albums, I then simplified the fp to just 'lastup' but starting today (13k pics) it started to take 2-3 seconds to load the frontpage.
Note: the user albums themselves scale very nicely and I don;t see any reason why cpg shouldnt be able to handle a couple millions pics ... it's only the frontpage that gets slower and slower the more pics (and albums) are in the database - but here's an example of a simple hack (anything else than great but works) to get that very fast as well:
I first added 2 new fields to the pic and comment table:
alter table cpg11d_pictures add is_recent enum ('YES', 'NO') not null;
alter table cpg11d_pictures add KEY (is_recent);
alter table cpg11d_comments add is_recent enum ('YES', 'NO') not null;
alter table cpg11d_comments add KEY (is_recent);
and: insert into cpg11d_config (name, value) values ('is_recent_interval', unix_timestamp() - 100000);
at the very end (row:746 in index.php) there is a:
// Speed-up the random image query by 'keying' the image table
on which I added some code:
if (time() - $CONFIG['is_recent_interval'] > 3600*4) {
// We set is_recent_interval to a higher interval right away so that other clients don't execute the following updates as well
$result = db_query("UPDATE {$CONFIG['TABLE_CONFIG']} SET value = '" . time() . "' WHERE name = 'is_recent_interval'");
$result = db_query("update {$CONFIG['TABLE_PICTURES']} SET is_recent='NO' WHERE (unix_timestamp()-ctime)>7200");
$result = db_query("update {$CONFIG['TABLE_COMMENTS']} SET is_recent='NO' WHERE (msg_date < DATE_SUB(NOW(), INTERVAL 4 hour))");
}
==> pics older than 2 hours and comments older than 4 hours are getting marked as "is_recent=NO' - these two update commands are getting executed every 4 hours (and because I set earlier is_recent_interval in the config to unix_timestamp() - 100000 it's getting executed the first time you refresh the FP right away).
Because my gallery has a pretty simple setup (n users with m albums) and no other categories I can simplify some things in get_pics_data in include/functions.inc.php:
switch($album){
case 'lastcom': // Last comments
...
// GJ_START
if ($ALBUM_SET || $keyword) {
$TMP_SET = str_replace($CONFIG['TABLE_PICTURES'],'p',$ALBUM_SET);
$result = db_query("SELECT $select_columns FROM {$CONFIG['TABLE_COMMENTS']} as c, {$CONFIG['TABLE_PICTURES']} as p WHERE approved = 'YES' AND c.pid = p.pid $keyword $TMP_SET ORDER by msg_id DESC $limit");
}
else { // frontpage quick hack version
$result = db_query("SELECT $select_columns FROM {$CONFIG['TABLE_COMMENTS']} as c, {$CONFIG['TABLE_PICTURES']} as p WHERE c.pid = p.pid AND c.is_recent='YES' ORDER by msg_id DESC $limit");
}
// GJ_END
...
...
case 'lastup': // Last uploads
if ($ALBUM_SET && $CURRENT_CAT_NAME) {
$album_name = $lang_meta_album_names['lastup'].' - '. $CURRENT_CAT_NAME;
} else {
$album_name = $lang_meta_album_names['lastup'];
}
// GJ_START
if ($ALBUM_SET) {
$result = db_query("SELECT COUNT(*) from {$CONFIG['TABLE_PICTURES']} WHERE approved = 'YES' $ALBUM_SET");
}
else { // frontpage 'quick hack' - no need to check 'approved' - simple count on table is much faster
$result = db_query("SELECT COUNT(*) from {$CONFIG['TABLE_PICTURES']}");
}
// GJ_END
$nbEnr = mysql_fetch_array($result);
$count = $nbEnr[0];
mysql_free_result($result);
if($select_columns != '*' ) $select_columns .= ',title, caption, owner_id, owner_name, aid';
// GJ_START
if ($ALBUM_SET) {
$result = db_query("SELECT $select_columns FROM {$CONFIG['TABLE_PICTURES']} WHERE approved = 'YES' $ALBUM_SET ORDER BY pid DESC $limit");
}
else { // frontpage 'quick hack'
$result = db_query("SELECT $select_columns FROM {$CONFIG['TABLE_PICTURES']} WHERE is_recent='YES' ORDER BY pid DESC $limit");
}
// GJ_END
==> I am using is_recent as an index with a much smaller potential result set
also in index.php I am completely dropping get_subcat:
// GJ_START
if (!$cat) {
get_subcat_data($cat, $cat_data, $album_set_array, $CONFIG['subcat_level']);
}
// GJ_END
(in get_cat_list)
that took a lot of time since some 4k album ids got returned (again: my gallery setup is very simple - don;t do this if you have categories!)
for the random function I did this: (in functions.inc.php)
if ($ALBUM_SET) {
$result = db_query("SELECT COUNT(*) from {$CONFIG['TABLE_PICTURES']} WHERE approved = 'YES' $ALBUM_SET");
}
else { // frontpage 'quick hack' - NOTE: this is going to return the highest pid id which is most likely not the number of pics
$result = db_query("SELECT MAX(pid) from {$CONFIG['TABLE_PICTURES']}");
}
...
...
if($select_columns != '*') $select_columns .= ', aid';
if (!$ALBUM_SET) {
$RANDOM_MULTIPLE = 2; // getting two times (at the most) as many random results as requested
$sql = "SELECT $select_columns FROM {$CONFIG['TABLE_PICTURES']} WHERE pid IN (";
for ($i=0; $i<$limit2*$RANDOM_MULTIPLE; $i++) {
$sql .= ($i ? ',' : '') . rand(0, $pic_count);
}
$sql .= ") LIMIT $limit2";
$result = db_query($sql);
}
else {
// if we have more than 1000 pictures, we limit the number of picture returned
...
}
$rowset = array();
while($row = mysql_fetch_array($result)){
...
==> to speed up the random function I am simply doing this:
- getting the highest (pid) for the picture table
- getting results for twice (at the most) as many pics as requested by simply randomly selecting pids between 0... and max_pid - that usually returns $limit2 results as requested in 99% of all cases and it's very fast - (if it really returns 14 instead of 15 requested results once in a while... who cares...)
(once a gallery gets really huge it'll make sense to randmly select only pids with a modulo of the weekday to optimize caching in the DB/picserver)
OK, so this made the frontpage very fast - but once again only modify your code like this if you have a very simple gallery setup and if you don;t care about the 'approved' status (here, pics just get deleted right away if not 'approved'
If you have a more complex setup you might use that as a suggestion where to start optimizing things...