This doesn’t directly solve the SQL_CALC_FOUND_ROWS issue, but below are general mysql performance rules that work well for WP on VPSs and you will get some performance increases and not have so much trouble with the one slow query.
It’s a good idea to use mysqltuner to log some traffic on your server and then adjust my.cnf with its recommendations rather than simply change a few cache settings. Some my.cnf settings need to be changed in conjunction with other memory use settings. See https://github.com/rackerhacker/MySQLTuner-perl and try it. It can make a huge difference in server and mysql performance. Run it on the command line.
Also, be sure you have cleaned your DB of post/page revisions; WP databases can get huge when saving multiple post revisions. I’ve dropped database sizes down to 10% of their original footprint with a corresponding huge increase in performance.
To delete revisions, run in phpmyadmin (make a DB backup first)
DELETE a,b,c
FROM wp_posts a
LEFT JOIN wp_term_relationships b ON (a.ID = b.object_id)
LEFT JOIN wp_postmeta c ON (a.ID = c.post_id)
WHERE a.post_type = 'revision'
and then optimize the DB. Or use one of many revision control plugins.
Use WordPress › WP Super Cache « WordPress Plugins with php caching (not mod_rewrite) for the best performance. W3TC is outdated and can conflict with caching on VPSs.
Use an php opcode cahce like APC.
You may also want to look into tuning apache and httpd.conf for the right number of http processes and child processes.
Thread Starter
TAJon
(@tajon)
Thank you for getting back to me.
I performed your recommendations and configured my my.cnf file as per MySQL Tuner recommendations. However, the problem is still happening so I activated the Slow Query Log.
I found the following results:
# Time: 121226 17:14:26
# User@Host: true_wp1[true_wp1] @ localhost []
# Query_time: 56.503083 Lock_time: 0.000188 Rows_sent: 5 Rows_examined: 23349642
use true_wp1;
SET timestamp=1356567266;
SELECT SQL_CALC_FOUND_ROWS yczrytsxi_posts.ID FROM yczrytsxi_posts INNER JOIN yczrytsxi_postmeta ON (yczrytsxi_posts.ID = yczrytsxi_postmeta.post_id)
INNER JOIN yczrytsxi_postmeta AS mt1 ON (yczrytsxi_posts.ID = mt1.post_id)
INNER JOIN yczrytsxi_postmeta AS mt2 ON (yczrytsxi_posts.ID = mt2.post_id) WHERE 1=1 AND yczrytsxi_posts.post_type IN (‘post’, ‘page’, ‘attachment’, ‘gab_gallery’) AND (yczrytsxi_posts.post_status = ‘publish’) AND (yczrytsxi_postmeta.meta_key = ‘iframe’
OR mt1.meta_key = ‘video’
OR mt2.meta_key = ‘videoflv’ ) GROUP BY yczrytsxi_posts.ID ORDER BY yczrytsxi_posts.post_date DESC LIMIT 0, 5;
I installed the Debug Queries Plugin which indicates that this query is called from:
Call from: require(‘wp-blog-header.php’), require_once(‘wp-includes/template-loader.php’), include(‘/themes/linepress/home.php’), include(‘/themes/linepress/home-primary-bottom.php’), WP_Query->query, WP_Query->get_posts
As I mentioned before I am not a MySQL or WordPress Code expert but it looks like it is my “Theme” making this query which is causing the issue.
I’ve also noticed that it is also only when on the “Home” page where this query is called but not on any other page (As far as I could see)
I am not sure why it is making this query and I am hoping someone can point me in the right direction. Is there a way to improve this query or should I submit a ticket with the people that developed the theme?
Thread Starter
TAJon
(@tajon)
Turns out it was an issue with the Theme. Logged a ticket with with the people that developed the theme and they send through a solution that fixed the problem.
Thank you for your help and guiding to use the correct tools.
Did you clean out revisions? Did you set up Super Cache? Did you set up an opcode cache for PHP? Those will all greatly increase performance on a VPS.
The SQL that you posted is from a filter or WP_Query that is doing a very expensive multiple meta_query. There is no reason to be doing 3 INNER JOINS on the post_meta table. This could probably be optimized to just require 1 INNER JOIN. Also if you don’t need pagination on the results you can set ‘no_found_rows’ => true which will change it from doing SQL_CALC_FOUND_ROWS to SELECT *.
For large sites expensive queries like this should be added to the persistent object cache using the WP_Cache class
$my_query = wp_cache_get( $key, $group );
if ( false === $my_query ) {
$my_query = new WP_Query( $args );
wp_cache_set( $key, $query, $group, 60 * 60 );
}
//Continue with loop
Hi Chris,
I have the same problem and it seems this is caused by my Classipress installation. One of my problematic queries looks like this:
SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts INNER JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id) INNER JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id)INNER JOIN wp_postmeta AS mt1 ON (wp_posts.ID = mt1.post_id)INNER JOIN wp_postmeta AS mt2 ON (wp_posts.ID = mt2.post_id)INNER JOIN wp_postmeta AS mt3 ON (wp_posts.ID = mt3.post_id)INNER JOIN wp_postmeta AS mt4 ON (wp_posts.ID = mt4.post_id)INNER JOIN wp_postmeta AS mt5 ON (wp_posts.ID = mt5.post_id)INNER JOIN wp_postmeta AS mt6 ON (wp_posts.ID = mt6.post_id)INNER JOIN wp_postmeta AS mt7 ON (wp_posts.ID = mt7.post_id)INNER JOIN wp_postmeta AS mt8 ON (wp_posts.ID = mt8.post_id)INNER JOIN wp_postmeta AS mt9 ON (wp_posts.ID = mt9.post_id)INNER JOIN wp_postmeta AS mt10 ON (wp_posts.ID = mt10.post_id)INNER JOIN wp_postmeta AS mt11 ON (wp_posts.ID = mt11.post_id)INNER JOIN wp_postmeta AS mt12 ON (wp_posts.ID = mt12.post_id) WHERE 1=1 AND ( wp_term_relationships.term_taxonomy_id IN (13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36) ) AND wp_posts.post_type IN (‘ad_listing’) AND (wp_posts.post_status = ‘publish’) AND ( (wp_postmeta.meta_key = ‘cp_price’ AND CAST(wp_postmeta.meta_value AS SIGNED) BETWEEN ’33’ AND ‘11746’)AND (mt1.meta_key = ‘cp_bezirk’ AND CAST(mt1.meta_value AS CHAR) IN (’17. Bezirk’))AND (mt2.meta_key = ‘cp_objekttyp’ AND CAST(mt2.meta_value AS CHAR) IN (‘Wohnung’))AND (mt3.meta_key = ‘cp_bau’ AND CAST(mt3.meta_value AS CHAR) IN (‘Neubau’))AND (mt4.meta_key = ‘cp_size’ AND CAST(mt4.meta_value AS CHAR) IN (’61 – 70 m²’,’71 – 80 m²’))AND (mt5.meta_key = ‘cp_zimmer’ AND CAST(mt5.meta_value AS CHAR) IN (‘2′,’3’))AND (mt6.meta_key = ‘cp_heizungsart’ AND CAST(mt6.meta_value AS CHAR) IN (‘Etagenheizung’,’Zentralheizung’))AND (mt7.meta_key = ‘cp_fahrstuhl’ AND CAST(mt7.meta_value AS CHAR) IN (‘Ja’))AND (mt8.meta_key = ‘cp_balkonloggia’ AND CAST(mt8.meta_value AS CHAR) IN (‘Ja’))AND (mt9.meta_key = ‘cp_terrasse’ AND CAST(mt9.meta_value AS CHAR) IN (‘Ja’))AND (mt10.meta_key = ‘cp_keller’ AND CAST(mt10.meta_value AS CHAR) IN (‘Ja’))AND (mt11.meta_key = ‘cp_garage’ AND CAST(mt11.meta_value AS CHAR) IN (‘Ja’))AND (mt12.meta_key = ‘cp_abstellplatz’ AND CAST(mt12.meta_value AS CHAR) IN (‘Ja’)) ) GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC LIMIT 0, 20
Even though I am no sql expert, this looks horrible. My hosting provider blocked my account because I was using too much server resources and queries like the above crashed the server.
Do you know of a good way to disable the SELECT SQL_CALC_FOUND_ROWS statement but still have pagination working?
Thanks a lot!
