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?
# 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;
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;
# 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;
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;
