Title: SQL_CALC_FOUND_ROWS Performance Issue
Last modified: August 20, 2016

---

# SQL_CALC_FOUND_ROWS Performance Issue

 *  Resolved [TAJon](https://wordpress.org/support/users/tajon/)
 * (@tajon)
 * [13 years, 5 months ago](https://wordpress.org/support/topic/sql_calc_found_rows-performance-issue/)
 * We have a webiste with +- 1000 posts and +- 11000 postmeta entries. We get +-
   20K – 40K visitors a day.
 * We’ve noticed a problem with SQL using up all resources on the VPS till eventually
   crashing.
 * After some investigation we’ve found the following query causing the issue.
 * **Copy to tmp table** – _SELECT SQL\_CALC\_FOUND\_ROWS wp\_posts.ID FROM wp\_posts
   INNER JOIN wp\_postmeta_
 * The query doesn’t seem to execute at specific times but I’ve noticed that when
   it starts it can eventually have 10+ lines of the same query in the MySQL process
   list and this is when the database crashes.
 * We’ve activated the query cache = 512MB and increased the temp_table_size to 
   512MB in my.cnf. This improved it but the problem eventually returns.
 * I’ve read that there are some very large WordPress sites that are experience 
   similar issues but they have 100K + posts… [http://wordpress.org/support/topic/slow-queries-sql_calc_found_rows-bringing-down-site?replies=25](http://wordpress.org/support/topic/slow-queries-sql_calc_found_rows-bringing-down-site?replies=25)
 * I am not a MySQL or WordPress Code expert but I was hoping someone can help me
   or guide me in the right direction getting this issue resolved.
 * I’ve already disabled most of the plugins but the problem still happens which
   indicates that the problem might be with WordPress.
 * Here is a post I found but I am not sure if this will resolve the issue or how
   to apply the code mentioned in the article. [http://wordpress.org/support/topic/select-sql_calc_found_rows-causing-crashes?replies=3](http://wordpress.org/support/topic/select-sql_calc_found_rows-causing-crashes?replies=3)
 * Thank you in advance.
 * Jon.

Viewing 6 replies - 1 through 6 (of 6 total)

 *  [Mark Ratledge](https://wordpress.org/support/users/songdogtech/)
 * (@songdogtech)
 * [13 years, 5 months ago](https://wordpress.org/support/topic/sql_calc_found_rows-performance-issue/#post-3305535)
 * 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](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](http://wordpress.org/extend/plugins/wp-super-cache/)
   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](https://wordpress.org/support/users/tajon/)
 * (@tajon)
 * [13 years, 5 months ago](https://wordpress.org/support/topic/sql_calc_found_rows-performance-issue/#post-3305565)
 * 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](https://wordpress.org/support/users/tajon/)
 * (@tajon)
 * [13 years, 5 months ago](https://wordpress.org/support/topic/sql_calc_found_rows-performance-issue/#post-3305585)
 * 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.
 *  [Mark Ratledge](https://wordpress.org/support/users/songdogtech/)
 * (@songdogtech)
 * [13 years, 5 months ago](https://wordpress.org/support/topic/sql_calc_found_rows-performance-issue/#post-3305586)
 * 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.
 *  [Chris Olbekson](https://wordpress.org/support/users/c3mdigital/)
 * (@c3mdigital)
 * [13 years, 5 months ago](https://wordpress.org/support/topic/sql_calc_found_rows-performance-issue/#post-3305587)
 * 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](http://codex.wordpress.org/Class_Reference/WP_Object_Cache)
 *     ```
       $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
       ```
   
 *  [cyjambo](https://wordpress.org/support/users/cyjambo/)
 * (@cyjambo)
 * [12 years, 10 months ago](https://wordpress.org/support/topic/sql_calc_found_rows-performance-issue/#post-3305642)
 * 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!

Viewing 6 replies - 1 through 6 (of 6 total)

The topic ‘SQL_CALC_FOUND_ROWS Performance Issue’ is closed to new replies.

## Tags

 * [performance](https://wordpress.org/support/topic-tag/performance/)

 * In: [Fixing WordPress](https://wordpress.org/support/forum/how-to-and-troubleshooting/)
 * 6 replies
 * 4 participants
 * Last reply from: [cyjambo](https://wordpress.org/support/users/cyjambo/)
 * Last activity: [12 years, 10 months ago](https://wordpress.org/support/topic/sql_calc_found_rows-performance-issue/#post-3305642)
 * Status: resolved

## Topics

### Topics with no replies

### Non-support topics

### Resolved topics

### Unresolved topics

### All topics