seems that caching and dbase calls are the impasse, sorry.

[acidride]

These were the SQL optimization revisions that I was going to use to upgrade my beta 9.0. Since I've made so many changes to my beta 9.0, I've got to add these improvements incrementally. I don't think I'll ever be able to just upgrade to the next version unfortunately :-( This list might help anyone just wanting to do the SQL optimizations only for an older version.

/config.php rev 850 - SQL Optimization
/config.php rev 865 - SQL Optimization (Large Site)
/install/install3.php rev 865 - SQL Optimization (Large Site)
/install/installtables.php rev 850 - SQL Optimization
/install/installtables.php rev 865 - SQL Optimization (Large Site)
/install/installtables.php rev 867 - SQL Optimization (Large Site)
/install/installtables.php rev 890 - SQL Optimization
/install/upgrade.php rev 850 - SQL Optimization
/install/upgrade.php rev 872 - SQL Optimization
/install/upgrade.php rev 865 - SQL Optimization (Large Site)
/install/upgrade.php rev 867 - SQL Optimization (Large Site)
/libs/comment.php rev 850 - SQL Optimization
/libs/dbtree.php rev 856 - SQL Optimization
/libs/dbtree.php rev 868 - SQL Bug Fix
/libs/html1.php rev 865 - SQL Optimization (Large Site)
/libs/html1.php rev 875 - SQL Optimization
/libs/link.php rev 844 - SQL Optimization
/libs/link.php rev 850 - SQL Optimization
/libs/link.php rev 859 - SQL Optimization
/libs/link.php rev 865 - SQL Optimization (Large Site)
/libs/pageview.php rev 890 - SQL Optimization
/libs/search.php rev 875 - SQL Optimization
/libs/smartyvariables.php rev 844 - SQL Optimization
/libs/smartyvariables.php rev 860 - User Cache
/libs/smartyvariables.php rev 865 - SQL Optimization (Large Site)
/libs/user.php rev 860 - User Cache
/libs/utils.php rev 866 - SQL Optimization (Large Site)
/libs/votes.php rev 859 - SQL Optimization
/rss/import_feeds.php rev 865 - SQL Optimization (Large Site)
/submit.php rev 865 - SQL Optimization (Large Site)
/user_add_remove_links.php rev 869 - SQL Optimization

[acidride]

Cache changes that I will implement are the following to get up to speed. I hope this list helps anyone just wanting to improve cache from Beta 9.0.

/admin_categories.php rev 533 - Cache / Optimization
/admin_categories.php rev 535 - Cache / Optimization
/config.php rev 533 - Cache / Optimization
/config.php rev 879 - Cache Code Upgrade
/libs/dbtree.php rev 887 - Cache Code Upgrade
/libs/html1.php rev 533 - Cache / Optimization
/libs/html1.php rev 880 - Cache Code Upgrade
/libs/html1.php rev 881 - Cache Code Upgrade
/libs/link.php rev 883 - Cache Code Upgrade
/libs/login.php rev 882 - Cache Code Upgrade
/libs/smartyvariables.php rev 533 - Cache / Optimization
/libs/smartyvariables.php rev 879 - Cache Code Upgrade
/libs/smartyvariables.php rev 880 - Cache Code Upgrade
/libs/user.php rev 893 - Cache Code Upgrade
/libs/user.php rev 914 - Cache Code Upgrade
/plugins/function.include_clipcache.php rev 533 - Cache / Optimization
/readme.txt rev 545 - Chmod Requirements
/rss/import_feeds.php rev 879 - Cache Code Upgrade
/templates/cratima/sidebar.tpl rev 533 - Cache / Optimization
/templates/cratima/sidebar_modules/wrapper2.tpl rev 533 - Cache / Optimization
/templates/cratima/user_center.tpl rev 545 - Cache / Optimization

[kbeeveer46]

Originally Posted by Simon

On the SVN changes, all I can suggest is looking for entries with "cache" in them. But no guarentee that will get all of them (there were a number of them as i recall, mostly made by Ashdigg).

On a related note, I was talking with savant yesterday, and he was just testing a datastore with pligg. Using a datastore should massively reduce query numbers for actions such as getting story data for the main page, as it will use 1 call to get all the data, rather than 1 + n stories calls. The downside is the 1 query will be a lot slower than the 9 quick calls (the saving on the front page with 8 stories was only 56ms) - however when the database gets busy, only having to call the database once will obviously be a big performance boost. I don't think this change will appear particularly soon, but it will be a good one!

One area we noticed a lot of queries were being generated on were comments. For example take a look at these 2 links:

http://news.oioplus.com/marketing/oi...ls-up-running/ - O comments, 14 queries
http://news.oioplus.com/politics/oio...ing-pligg-cms/ - 3 comments, 46 queries

Just wanted to give a heads up on that one, as with a lot of comments will rack up the queries very quickly. Looks like that will need optimisation, or caching, or both.

I think Ash was wanting to work on the story page next and do some of the same things he did for the index and upcoming pages.

[savant]

I'm stopped work on the linkmanager.datastore so we don't end up doing the same thing.

[Simon]

Just upgraded to the latest 9.5 code - now reading only 17 queries (13 when logged in) on the front page, with 8 stories present. I am using a totally static sidebar, but still a great drop from the 50 queries or so I was seeing with the SVN code from a few weeks back, and of course a MASSIVE drop from 9.1.

NB: figures taken from pligg's internal query counting system

[rparks]

17 queries? That's awesome!
I just joined (haven't installed the app yet)
Was that query count for an out-of-the-box version or a custom-tweaked installation?