I have a large database with 150.000+ links and 8.000+ categories. I want to add even more data - there will be over 300.000 links, but i see that i am having problems with the page loading speed.
As of now, the category pages need 4-6 seconds to load - this is too slow anyways and i can imagine what happens if i will have even more links. I see, however, that the most time is used to do unnecessary database queries and i would like to try exclude them.
Take a look at all queries starting at 0.82 seconds - all produce 0 results ! I would like to eliminate them somehow if i would know where they are.
Load time so far: 0.34 seconds. Performing query: SELECT id,name,parent,validated,description,time,parentnames,parentids,numlinks,hide,lastlinktime,custom,lastedit,moderators,headerinfo,related,numsub,type,permissions,subscribers,subscriber,mixtypes,isalbum,orderlinks,totalcomments,ulsearchmax,ulsearchquery,ulsearchadult,alias,catcols,linkcols,dmozpath,sponsorlinks,views,customwrap FROM wsnlinks_categories WHERE id=20 ;
Load time so far: 0.34 seconds. Performing query: LOCK TABLES wsnlinks_email WRITE, wsnlinks_links WRITE, wsnlinks_categories WRITE, wsnlinks_members WRITE;
Load time so far: 0.34 seconds. Performing query: SELECT id,`to`,subject,message,headers,replacement,mime FROM wsnlinks_email WHERE timesent=0 ORDER BY id ASC LIMIT 0,10;
Number of rows is 0 for Resource id #41
Load time so far: 0.34 seconds. Performing query: UNLOCK TABLES;
Load time so far: 0.34 seconds. Performing query: UPDATE wsnlinks_categories SET views='81' WHERE id=20;
Load time so far: 0.35 seconds. Performing query: SELECT id,name,parent,validated,description,time,parentnames,parentids,numlinks,hide,lastlinktime,custom,lastedit,moderators,headerinfo,related,numsub,type,permissions,subscribers,subscriber,mixtypes,isalbum,orderlinks,totalcomments,ulsearchmax,ulsearchquery,ulsearchadult,alias,catcols,linkcols,dmozpath,sponsorlinks,views,customwrap FROM wsnlinks_categories WHERE parent=20 AND validated=1 AND hide=0 AND isalbum=0 ORDER BY name ASC LIMIT 0,100;
Number of rows is 100 for Resource id #44
Load time so far: 0.81 seconds. Performing query: SELECT id FROM wsnlinks_links WHERE catid=20 AND hide=0 AND validated=1 ;
Number of rows is 0 for Resource id #45
Load time so far: 1.94 seconds. Performing query: SELECT id FROM wsnlinks_links WHERE catid=20 AND validated=1 AND hide=0 AND type='regular' ;
Number of rows is 0 for Resource id #46
Load time so far: 3.09 seconds. Performing query: SELECT id FROM wsnlinks_links WHERE catid=20 AND hide=0 AND validated=1 ;
Number of rows is 0 for Resource id #47
Load time so far: 3.09 seconds. Performing query: SELECT id FROM wsnlinks_links WHERE catid=20 AND validated=0 ;
Number of rows is 0 for Resource id #48
Load time so far: 4.27 seconds. Performing query: SELECT id FROM wsnlinks_comments WHERE catid=20 AND validated=0 ;
Number of rows is 0 for Resource id #49
Load time so far: 4.35 seconds. Performing query: SELECT COUNT(*) FROM wsnlinks_sessions WHERE 1=1 AND (catid=20 AND memberid>0) ;
Load time so far: 4.35 seconds. Performing query: SELECT id,memberid,location,time,ip,browser,starttime,catid,threadid,areaname FROM wsnlinks_sessions WHERE 1=1 AND (catid=20 AND memberid>0) ORDER BY time DESC LIMIT 0,100;
0 is a natural result. There's no way to know it'll be 0 until checking.
You have a {REGULARTOTAL} in the template that's causing a long one there I think. Category views (is that switched on?) is causing another.
You don't have any alias-related queries there. As for online users, it ought to not do that but you can take it out of the template to prevent two of those queries.
Paul wrote: 0 is a natural result. There's no way to know it'll be 0 until checking.
I understand that, but its almost identical wueries that will always return 0 as i don't plan to change anything in the future - any chance to exclude them ? Where do they come from ?
Comments on Get rid of unnedded querys
Beginner
Usergroup: Customer
Joined: Feb 19, 2005
Total Topics: 4
Total Comments: 8
Hi !
I have a large database with 150.000+ links and 8.000+ categories. I want to add even more data - there will be over 300.000 links, but i see that i am having problems with the page loading speed.
As of now, the category pages need 4-6 seconds to load - this is too slow anyways and i can imagine what happens if i will have even more links. I see, however, that the most time is used to do unnecessary database queries and i would like to try exclude them.
Take a look at all queries starting at 0.82 seconds - all produce 0 results ! I would like to eliminate them somehow if i would know where they are.
Load time so far: 0.34 seconds.
Performing query: SELECT id,name,parent,validated,description,time,parentnames,parentids,numlinks,hide,lastlinktime,custom,lastedit,moderators,headerinfo,related,numsub,type,permissions,subscribers,subscriber,mixtypes,isalbum,orderlinks,totalcomments,ulsearchmax,ulsearchquery,ulsearchadult,alias,catcols,linkcols,dmozpath,sponsorlinks,views,customwrap FROM wsnlinks_categories WHERE id=20 ;
Load time so far: 0.34 seconds.
Performing query: LOCK TABLES wsnlinks_email WRITE, wsnlinks_links WRITE, wsnlinks_categories WRITE, wsnlinks_members WRITE;
Load time so far: 0.34 seconds.
Performing query: SELECT id,`to`,subject,message,headers,replacement,mime FROM wsnlinks_email WHERE timesent=0 ORDER BY id ASC LIMIT 0,10;
Number of rows is 0 for Resource id #41
Load time so far: 0.34 seconds.
Performing query: UNLOCK TABLES;
Load time so far: 0.34 seconds.
Performing query: UPDATE wsnlinks_categories SET views='81' WHERE id=20;
Load time so far: 0.35 seconds.
Performing query: SELECT id,name,parent,validated,description,time,parentnames,parentids,numlinks,hide,lastlinktime,custom,lastedit,moderators,headerinfo,related,numsub,type,permissions,subscribers,subscriber,mixtypes,isalbum,orderlinks,totalcomments,ulsearchmax,ulsearchquery,ulsearchadult,alias,catcols,linkcols,dmozpath,sponsorlinks,views,customwrap FROM wsnlinks_categories WHERE parent=20 AND validated=1 AND hide=0 AND isalbum=0 ORDER BY name ASC LIMIT 0,100;
Number of rows is 100 for Resource id #44
Load time so far: 0.81 seconds.
Performing query: SELECT id FROM wsnlinks_links WHERE catid=20 AND hide=0 AND validated=1 ;
Number of rows is 0 for Resource id #45
Load time so far: 1.94 seconds.
Performing query: SELECT id FROM wsnlinks_links WHERE catid=20 AND validated=1 AND hide=0 AND type='regular' ;
Number of rows is 0 for Resource id #46
Load time so far: 3.09 seconds.
Performing query: SELECT id FROM wsnlinks_links WHERE catid=20 AND hide=0 AND validated=1 ;
Number of rows is 0 for Resource id #47
Load time so far: 3.09 seconds.
Performing query: SELECT id FROM wsnlinks_links WHERE catid=20 AND validated=0 ;
Number of rows is 0 for Resource id #48
Load time so far: 4.27 seconds.
Performing query: SELECT id FROM wsnlinks_comments WHERE catid=20 AND validated=0 ;
Number of rows is 0 for Resource id #49
Load time so far: 4.35 seconds.
Performing query: SELECT COUNT(*) FROM wsnlinks_sessions WHERE 1=1 AND (catid=20 AND memberid>0) ;
Load time so far: 4.35 seconds.
Performing query: SELECT id,memberid,location,time,ip,browser,starttime,catid,threadid,areaname FROM wsnlinks_sessions WHERE 1=1 AND (catid=20 AND memberid>0) ORDER BY time DESC LIMIT 0,100;
Number of rows is 0 for Resource id #52
----------------------------------------------------
Also, i see the queries for the userlists and aliases. I have switched both off in the switches, but they are still there. How to eliminate them too ?
Thank you !
developer
Usergroup: Administrator
Joined: Dec 20, 2001
Location: Diamond Springs, California
Total Topics: 61
Total Comments: 7867
0 is a natural result. There's no way to know it'll be 0 until checking.
You have a {REGULARTOTAL} in the template that's causing a long one there I think. Category views (is that switched on?) is causing another.
You don't have any alias-related queries there. As for online users, it ought to not do that but you can take it out of the template to prevent two of those queries.
Beginner
Usergroup: Customer
Joined: Feb 19, 2005
Total Topics: 4
Total Comments: 8
0 is a natural result. There's no way to know it'll be 0 until checking.
I understand that, but its almost identical wueries that will always return 0 as i don't plan to change anything in the future - any chance to exclude them ? Where do they come from ?
developer
Usergroup: Administrator
Joined: Dec 20, 2001
Location: Diamond Springs, California
Total Topics: 61
Total Comments: 7867
Most of those are things like emails, subcategories, unvalidated items.