Hi,
Just waned to share my experience about coppermine performance with a lot of photo around 130 000 images and share some tips and recommendations.
1. Missing indexes
functions.inc.php 1603 line.
Top rated sorting is missing triple column index on images table.
ORDER BY pic_rating $DESC, r.votes $DESC, pid $DESC
KEY pic_rate_sort (pic_rating,votes,pid)
Run SELECT EXPLAIN... and you will see about what I'm talking about.
Second thing i do not like.
ORDER BY mtime $DESC, pid $ASC
You are mixing DESC and ASC sorting. That way if you even and create mtime and pid index. It's useless.... Because sorting is mixing ASC and DESC... With 130 000 images it makes different about half second.
See
http://www.mysqlperformanceblog.com/2006/09/01/order-by-limit-performance-optimization/ Sort in one direction paragraph. If you have to sort in custom directions just create reverse_index column and sort in one direction...
$sort_array = array(
'na' => "filename $ASC",
'nd' => "filename $DESC",
'ta' => "title $ASC",
'td' => "title $DESC",
'da' => "pid $ASC",
'dd' => "pid $DESC",
'pa' => "position $ASC",
'pd' => "position $DESC",
);
Missing indexes:
filename - we can use let say 6 characters index here.
position - missing index
title - we can use let say 6 characters index here.
If you dig deeper you could find even more places where indexes are missing just edit mysql config that it logs all queries not using index.
2. General ideas
I have attempted to cache some parts of coppermine with memcache, but in general it's useless because of current coppermine architecture... I dowt that it's possible with current coppermine architecture witch is very inflexible.
If you try to cache with ETag you will endup with the same problem...
So please consider atleast implementing some changes based on first part of suggestions. It will give some boost with a lot of images.