[mihirp]

Hi,

Currently I have around 13,000 images and around 300 categories. The site seems pretty slow.

Under a category which has around 200 sub-cats, it takes around 9 seconds to load.

The following is the query:

Page generated in 9.83779 seconds with 15 queries
1: SELECT varname,setting FROM pp_settings (0.00106 sec)
2: SELECT u.userid,u.username,u.email,u.password,u.usergroupid,u.timezoneoffset,s.sessionhash,u.salt,s.lastactivity,u.membergroupids FROM user u LEFT JOIN session s ON s.userid=u.userid WHERE u.userid=1 (0.00065 sec)
3: UPDATE session SET lastactivity = '1173089027' , location = '/gallery/showgallery.php/cat/506' WHERE userid='1' (0.00015 sec)
4: SELECT groupid,cpaccess,modaccess,diskspace,uploadsize,uplimit,uploads,comments,editpho,editposts,useralbums,reqmod,canassign FROM pp_usergroups WHERE groupid=6 LIMIT 1 (0.00054 sec)
5: SELECT id,ugnoupload,ugnoanno,ugnoview,ugnopost,ugnovote,password,watermark,cattype,ismember,catname,parent,children,thumbs,canzip,moderators,header,footer,headtags,subalbum FROM pp_categories (0.00333 sec)
6: SELECT c.id,c.catname,c.thumbs,c.cattype,c.parent,c.theme,c.thumbtype, c.sortorder,c.frames,c.intro,c.introtitle,c.introcopy,c.ratingtype,c.takevotes,c.ismember,n.userid FROM pp_categories c LEFT JOIN pp_notify n ON (n.userid='1' AND n.photo=c.id AND n.type=1) WHERE c.id=506 (0.00152 sec)
7: REPLACE INTO pp_laston VALUES('506', '1', '1173089027') (0.00024 sec)
8: SELECT * FROM pp_sort WHERE sortid=2 (0.00016 sec)
9: SELECT c.id,c.parent,c.catname,c.description,c.photos,c.private, p.userid, p.user, p.bigimage, p.cat, p.storecat, p.approved, p.width, p.height, p2.bigimage, p2.cat, p2.storecat, p2.userid, p2.user FROM pp_categories c LEFT JOIN pp_photos p ON p.id=c.lastphoto LEFT JOIN pp_photos p2 ON p2.id=c.thumbnail WHERE ( (c.children=506 OR c.subalbum=506) AND c.cattype='a' AND c.photos > 0 ) (0.00125 sec)
10: SELECT c.id, c.description, c.catorder, c.photos, c.posts, c.catdepth, p.id, p.bigimage, p.cat, p.approved, p.user, p.userid, p.date, p.width, p.height, p2.bigimage, p2.cat, p2.storecat, p2.userid, p2.width, p2.height, com.username, com.id, com.date, com.photo FROM pp_categories c LEFT JOIN pp_photos p ON c.lastphoto=p.id LEFT JOIN pp_photos p2 ON c.thumbnail=p2.id LEFT JOIN pp_comments com ON com.id=c.lastpost ORDER BY c.catorder ASC (0.00672 sec)
11: SELECT cat,laston FROM pp_laston WHERE userid='1' (0.00041 sec)
12: SELECT item, count(*) FROM pp_iponline GROUP BY item (0.00018 sec)
13: SELECT id,user,userid,cat,storecat,date,title,description,keywords,bigimage,width,height,filesize,views,medwidth,medheight,medsize,approved,rating,allowprint,numcom,lastposter FROM pp_photos WHERE approved < 2 AND cat=506 ORDER BY disporder,date DESC (0.00743 sec)
14: REPLACE INTO pp_iponline (userid,ipaddr,date,what,item) VALUES ('xxx','xxx','1173089028','showgallery','506') (0.00034 sec)
15: SELECT userid,date FROM pp_iponline WHERE item=506 ORDER BY date DESC (0.00020 sec)

How can I speed it up?

I dread to think what may happen if the website gets around 100,000+ photos with around 5000+ categories.

The server is pretty powerful : Dual processor + 1gb ram + its a dedicated machine only for this website and I have not yet opened it for general public. Meaning there are no viewers other than myself.