My FAP installation has exceeded 12,000 images and it is significantly slowing down the entire page during high traffic times. Here are some mysql queries from my slow query log. It is worth noting that every slow query originates from the album.

I desperately need some help speeding things up. I don't use any of the special features so I think I could get rid of the rating system (although I don't know how). And are the total images for each category counted dynamically? Wouldn't it be faster to just make a column in the cat table that automatically increments and decrements when an image is uploaded or deleted?

Code: [Download] [Hide] [Select]
# Query_time: 5 Lock_time: 0 Rows_sent: 4 Rows_examined: 71527
use forum;
SELECT p.*, u.user_id, u.username, r.rate_pic_id, AVG(r.rate_point) AS rating, COUNT(DISTINCT c.comment_id) AS comments
FROM phpbb_album AS p
LEFT JOIN phpbb_users AS u ON p.pic_user_id = u.user_id
LEFT JOIN phpbb_album_cat AS ct ON p.pic_cat_id = ct.cat_id
LEFT JOIN phpbb_album_rate AS r ON p.pic_id = r.rate_pic_id
LEFT JOIN phpbb_album_comment AS c ON p.pic_id = c.comment_pic_id
WHERE p.pic_cat_id IN (4,253,375,423,1,205,281,282,275,277,280,278,279,276,211,283,285,284,18,2,120,119,351,19,303,304,125,126,121,127,123,122,12$
GROUP BY p.pic_id
ORDER BY pic_time DESC
LIMIT 4;


Code: [Download] [Hide] [Select]
# Query_time: 4 Lock_time: 0 Rows_sent: 410 Rows_examined: 15635
SELECT c.*, COUNT(p.pic_id) AS count, '' AS username
FROM phpbb_album_cat AS c
LEFT JOIN phpbb_album AS p ON c.cat_id = p.pic_cat_id
WHERE cat_id <> 0 AND c.cat_user_id = 0
GROUP BY cat_id ORDER BY cat_title ASC;