performance tweaking on huge site

[aod]

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

[foulu]

I finished this work. Please mark as done !

[SaWey]

Marked as suggested