Dear friends,
I converted an album site with a proprietary software to coppermine, now I have 10K users, 14K albums and 300K photos in coppermine (album.click21.com.br). I noticed some very slow querys in mysqldumpslow:
Count: 1 Time=14.00s (14s) Lock=0.00s (0s) Rows=8.0 (Cool, root[root]@localhost
SELECT *, cpg1410_pictures.votes FROM cpg1410_pictures
LEFT JOIN cpg1410_albums ON
cpg1410_pictures.aid = cpg1410_albums.aid
WHERE approved = 'S' AND cpg1410_pictures.votes >= 'S' AND cpg1410_albums.aid IN (N,,{repeated 14809 times}N) ORDER BY pic_rating DESC, cpg1410_pictures.votes DESC, pid DESC LIMIT N ,N
Count: 1 Time=14.00s (14s) Lock=0.00s (0s) Rows=8.0 (Cool, root[root]@localhost
SELECT *,cpg1410_pictures.votes FROM cpg1410_pictures
LEFT JOIN cpg1410_albums ON
cpg1410_pictures.aid = cpg1410_albums.aid
WHERE approved = 'S' AND hits > N AND cpg1410_albums.aid IN (N,,{repeated 14809 times}N) ORDER BY hits DESC, filename LIMIT N ,N
Count: 1 Time=14.00s (14s) Lock=0.00s (0s) Rows=8.0 (Cool, root[root]@localhost
SELECT *, cpg1410_pictures.votes FROM cpg1410_pictures
LEFT JOIN cpg1410_albums ON
cpg1410_pictures.aid = cpg1410_albums.aid
WHERE approved = 'S' AND cpg1410_pictures.votes >= 'S' AND cpg1410_albums.aid IN (N,,{repeated 14810 times}N) ORDER BY pic_rating DESC, cpg1410_pictures.votes DESC, pid DESC LIMIT N ,N
Count: 4 Time=6.75s (27s) Lock=0.00s (0s) Rows=8.0 (32), root[root]@localhost
SELECT
cpg1410_albums.aid
,cpg1410_pictures.*
,cpg1410_albums.title as title
FROM cpg1410_albums
INNER JOIN cpg1410_pictures ON
cpg1410_pictures.aid = cpg1410_albums.aid
INNER JOIN cpg1410_users ON
cpg1410_pictures.owner_id = cpg1410_users.user_id
INNER JOIN click21_users ON
click21_users.id = cpg1410_users.user_id WHERE N=N AND approved = 'S' group BY cpg1410_albums.aid ORDER BY pid DESC LIMIT N ,N
Is there a way I could improve that? I know the delay is caused by coppermine topn,2/toprated,2 in album view, but even lastalb,2/lastup,2 makes the first page slow! How coul I improve these queries? I am willing to pay for support on this. Im using cpg1414
I finished this work. Please mark as done !
Marked as suggested