Question:
My page has runs a ton of queries. How can I cut this down?
Answer:
Check out the related entry on Optimizing EE.
Tweaking EE
Here are some things you can do to reduce queries.
* Don’t run sessions. Use cookies only. (session preferences)
* Disable “Secure Form Mode”. (session preferences)
* Turn off Referrer tracking. (referrer preferences)
* Enable SQL Query caching (database preferences)
* Enable Dynamic Weblog Query Caching (global weblog prefs)
* Enable page caching.
* Keep your templates as light as possible.
* Do not use embedded templates.
* If you need to use embedded content, use global variables instead of embedded templates if possible
* Don’t needlessly use the database. For example, don’t use a separate blog to run a link list.
* Use the “tag disabling” feature, which lets you turn off things in your weblog tag that you may not be using.
* Don’t use plugins which require database access.
* Turn off ancillary things, like CAPTCHAs, which require database queries.
* Disable stats collection. This you have to remove from the scripts. Open core/core.system.php and comment out $STAT->update_stats();
By doing the above things, a default installation of EE can be brought down to 4 queries.
Tweaking the Server
* make sure you have query caching turned on in EE
* make sure you have the proper permissions on your caching folders in EE because even if you have caching turned on, if EE can’t write the cache files its not going to help you at all.
* make sure you have your templates cached on your busiest templates, and also important is to cache template you maybe embedding like headers/footers as they are going to get hit on every page, so set the cache for a good period of time say 60 minutes.
* if you do have a busy site, make sure your host is using MySQL 4.0.x or 4.1.x with query caching turned on in MySQL as MySQL will then cache the most frequently requested queries which can limit general cpu overhead on the hosts database server, as well as speed queries that do make it to the MySQL server instead of EEs query cache.
Also experiment with turning persistent connections off on your EE install, the way some MySQL servers are configured, non-persistent connections can be the same speed as persistent but non-persistent connections on a busy MySQL server use up less CPU overhead as the server does not have to keep track of the persistent connections.
Thank you to Rick and Nevin. =)