5.) I have found a way to improve that query that MAY be useful for most users.
Query to improve:
SELECT pid, filepath, filename, url_prefix, filesize, pwidth, pheight, ctime, aid, keywords, votes, pic_rating, title, caption,hits,owner_id,owner_name from cpg1411_pictures WHERE ((aid='13' ) ) ORDER BY title DESC LIMIT 0 ,12 (0.745s)
What causes most of this slow-ness is the ORDER BY.. Order by hogs resources, so what we are going to do is REMOVE the sort order. Now, if you want to save weight in your php, remove all the sort order functions / etc.
Removing the sort order is going to cause the records to appear as they were accessed, so your oldest pictures will appear first. This doesn't work for my gallery, so I will be posting another method later. After than I'm going to attack the queries without indexes.
Open Functions.inc.php and find:
SELECT $select_columns from {$CONFIG['TABLE_PICTURES']} WHERE ((aid='$album' $forbidden_set_string ) $keyword) $approved $ALBUM_SET ORDER BY $sort_order $limit
Replace with:
SELECT $select_columns from {$CONFIG['TABLE_PICTURES']} WHERE ((aid='$album' $forbidden_set_string ) $keyword) $approved $ALBUM_SET $limit
Resulting query (under much higher load than the original query showed):
SELECT pid, filepath, filename, url_prefix, filesize, pwidth, pheight, ctime, aid, votes, pic_rating, title, caption,hits,owner_id,owner_name from cpg1411_pictures WHERE ((aid='12' ) ) LIMIT 0 ,12 (0.001s)
It's far from perfect, but your looking at a 745% improvement. I haven't added an index on it yet, so I'm guessing that when I change the index from approved, the query will be even faster.