SQL_CALC_FOUND_ROWS is deprecated as of MySQL 8.0.17

[javorszky]

Per ​https://dev.mysql.com/doc/refman/8.0/en/information-functions.html#function_found-rows

The SQL_CALC_FOUND_ROWS query modifier and accompanying FOUND_ROWS() function are deprecated as of MySQL 8.0.17 and will be removed in a future MySQL version. As a replacement, considering executing your query with LIMIT, and then a second query with COUNT(*) and without LIMIT to determine whether there are additional rows.

This is not yet immediately important because most hosts are on 5.5, or 5.6, rarely 5.7, but given the speed with which trac tickets move that impact very core functionalities, I thought it best to open this ticket to get the work started.

This impacts all the 6 places where it's being used, though one of them is in the WP_Query definition.

[johnbillion]

@javorszky Is this something you're willing to work on? As SQL_CALC_FOUND_ROWS can get slow for a large data set, does the replacment SELECT COUNT(*) WHERE ... method recommended in the MySQL docs have a performance advantage?

[javorszky]

No. For various reasons I have stepped back from contributing to WordPress in any way except for this ticket via comments.

Regarding the performance advantage, as long as the select count(column) from table where ... happens on indices, and uses only one column, it should be okay.

Also per mysql docs, there's not much in terms of alternatives.

Alternative is to not have the number of total rows available any more, which I suspect would break bc.

[morgantocker]

Former MySQL Product Manager here. Just a reply to this question specifically:

does the replacment SELECT COUNT(*) WHERE ... method recommended in the MySQL docs have a performance advantage?

Yes it can. What happens with SQL_CALC_FOUND_ROWS is that it disables the optimizations that MySQL can apply when there is a LIMIT.

The corresponding COUNT(*) query will have its own optimizations available too, such as covering indexes.

So the problem with SQL_CALC_FOUND_ROWS is you kind of get the worst of both worlds - with neither types of optimizations applying. Issuing two queries means one more network roundtrip, but on the MySQL-side both of the queries will be more efficient.

Signed-off-by: Morgan Tocker <tocker@…>

Trac ticket: https://core.trac.wordpress.org/ticket/47280

[wpe_bdurette]

Awesome to see this is happening. The GitHub PR only includes fixes for two of the occurrences SQL_CALC_ROWS_FOUND. There are others. I'm working on a PR to address all instances in core.

The usage in class-wp-query.php as some interesting implications. There is a filter on the posts query allowing for a completely arbitrary query to be run. Options that come to mind are to try to parse that query to replace the fields with COUNT(*) or simply wrap the query (e.g., SELECT COUNT(*) from ($query) p). For safety, I plan to do the latter, but I have no idea how that will play with the query optimizer.

Edit: Realizing the above won't work. At a minimum I need to strip any LIMIT clauses off the original query.

[morgantocker]

@wpe_bdurette I started a reply before seeing your edit. But I will include it here since it might still be useful.

Consider this example:

CREATE TABLE t1 (id INT NOT NULL);
INSERT INTO t1 VALUES (1),(2),(3),(4),(5);
SELECT * FROM t1 LIMIT 3;

mysql> SELECT COUNT(*) FROM (SELECT * FROM t1 LIMIT 3) p; # applies LIMIT, returns incomplete total
+----------+
| COUNT(*) |
+----------+
|        3 |
+----------+
1 row in set (0.00 sec)


mysql> SELECT COUNT(*) FROM (SELECT * FROM t1) p; # correct
+----------+
| COUNT(*) |
+----------+
|        5 |
+----------+
1 row in set (0.00 sec)

In terms of efficiency:

In MySQL 5.7+ it is just as efficient as rewriting the query to not use the subquery. You should be able to see this in explain:

mysql [localhost:8022] {msandbox} (test) > explain SELECT COUNT(*) FROM (SELECT * FROM t1) p;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    5 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql [localhost:8022] {msandbox} (test) > show warnings;
+-------+------+---------------------------------------------------------------+
| Level | Code | Message                                                       |
+-------+------+---------------------------------------------------------------+
| Note  | 1003 | /* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` |
+-------+------+---------------------------------------------------------------+
1 row in set (0.00 sec)

The optimization that rewrites subqueries in the from clause is called "derived_merge". It is a MySQL 5.7+ feature, and enabled by default. Earlier versions will be less efficient:

mysql [localhost:8022] {msandbox} (test) > set optimizer_switch="derived_merge=off";
Query OK, 0 rows affected (0.00 sec)

mysql [localhost:8022] {msandbox} (test) > explain SELECT COUNT(*) FROM (SELECT * FROM t1) p;
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table      | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | PRIMARY     | <derived2> | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    5 |   100.00 | NULL  |
|  2 | DERIVED     | t1         | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    5 |   100.00 | NULL  |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------+
2 rows in set, 1 warning (0.00 sec)

mysql [localhost:8022] {msandbox} (test) > show warnings;
+-------+------+-------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                 |
+-------+------+-------------------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | /* select#1 */ select count(0) AS `COUNT(*)` from (/* select#2 */ select `test`.`t1`.`id` AS `id` from `test`.`t1`) `p` |
+-------+------+-------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

I don't think the worse performance in 5.6 and lower will be that impactful for the typical sized database, but I will let others judge that one. There is technically a difference.

[wpe_bdurette]

Thanks @morgantocker! That's super helpful.

Whether or not I use a subselect or just replace the select_expr from the original query, I'm going to have to do some surgery on a query string in PHP. This is less than ideal without a full parser. It strikes me as safer to strip the LIMIT clause and move to a subselect, so that's what I'll do.

[hellofromTonya]

With 5.7 Beta 1 landing in less than 6 days and no activity, we agreed during core scrub today to punt this ticket to Future Release. Marking it as early for 5.8.

If any maintainer or committer feels this can be resolved in time, or wishes to assume ownership during a specific cycle, feel free to update the milestone accordingly.

[madpeter]

#54077 was marked as a duplicate.

[madpeter]

This issue missed 5.8 and has been open for more than 2 years now.

[pembo13]

Is this being actively worked on? Luckily, it doesn't look like MariaDB has deprecated this as well.

[johnbillion]

I wonder if we could get more clever by lazily populating the found_posts and max_num_pages properties only when one of them is accessed. The existing SELECT FOUND_ROWS() query needs to run immediately after the query you're counting the results for, but the corresponding SELECT COUNT(...) does not.

This way if a query runs but its found_rows or max_num_pages properties are never subsequently read then the query to count the results never needs to run.

The count_found_rows argument for the query classes should remain in case there's a need to prevent the found_posts and max_num_pages properties from being populated.

• Need to account for PHP serialization and JSON serialization of the query object.
• Probably need to convert found_posts and max_num_pages into private properties and then expose them via __get() which lazily populates (and caches) their values.
• Need tests for it all.
• Need to account for third party search integrations (eg. Elasticsearch) that override the SQL query to count results, should be handled by the existing found_posts_query filter.

I can work on this during 6.0 unless someone else wants to.

Trac ticket: https://core.trac.wordpress.org/ticket/47280

This continues the work of @morgo on #330. Additions:

• [X] Convert found_posts and max_num_pages into private properties and then expose them via __get() which lazily populates (and caches) their values
• [X] Tests for basic queries

Todo:

• [ ] Account for PHP serialization and JSON serialization of the WP_Query object -- the newly private properties still need to be included
• [ ] Many more tests for complex queries
• [ ] Test with third party search integrations (eg. Elasticsearch) that override the SQL query to count results, should be handled by the existing found_posts_query filter
• [ ] Repeat the process for all query classes that can perform count queries:
  • [X] WP_Query
  • [ ] WP_Network_Query
  • [ ] WP_Site_Query
  • [ ] WP_Comment_Query

[johnbillion]

@morgantocker @wpe_bdurette The PR at ​https://github.com/WordPress/wordpress-develop/pull/2119 is still heavily in progress but I wouldn't mind a review on it. So far I've been focusing on the lazy loading aspect of the found_posts and max_num_pages properties, and soon I'll focus on ensuring that the switch from SELECT FOUND_ROWS() to the unbounded COUNT works as expected for as many different query arguments as is reasonably possible.

So far I've added tests and query adjustments for simple queries:

• Basic query
• No limits: posts_per_page => -1 and nopaging => true (no LIMIT clause)
• Paginated query: paged => 3 (LIMIT with an offset)
• Author query: author => {id} (basic field match of post_author = {id})

And queries that result in joins:

• Term query: tag => {id} (LEFT JOIN with GROUP BY ID)
• Meta query: meta_key => {key} (INNER JOIN with GROUP BY ID)

Do you have suggestions for other clause types that can affect the accuracy of the SELECT COUNT query that we're using? It's generated by re-running the same query without its GROUP BY, ORDER BY, and LIMIT clauses: ​https://github.com/johnbillion/wordpress-develop/blob/26174be996cd46f1e99788b1dccfce6adaf921a8/src/wp-includes/class-wp-query.php#L3027-L3029

Once the changes for WP_Query are in a good place, the process can be repeated for the other query classes.

[xParham]

Hey @johnbillion, can we also maybe add count as an option for the fields query parameter that would then only run the COUNT query and populate the found_posts/total_users values and skip running the main query to save some query time in cases where only the count is needed?

[misterwebdeveloper]

Hi,

Experienced software developer of 20 years here. I just want to start by saying MySQL is a crazy good open source platform and kudos to everyone involved building it.

Sorry if I'm posting this in the wrong place...

Let me play devil's advocate.

As far as I can see the recommendation is to break DRY and duplicate SELECT statements.
Presumably this feature was orignally added to prevent that.

The MYSQL recommendation: duplicate the original query. So a SQL statement using SQL_CALC_FOUND_ROWS is 150 lines, most of those lines should be duplicated? This isn't a pragmatic solution. What if an existing system is using 50k lines? Is there something I'm missing here?

Another suggestion is to rewrite systems to use lazy loading. Not all systems are lazily loaded social media platforms. Pagination still has it's place and isn't going anywhere long term.

Is it possible to fix/improve the performance issues in this feature? How about MySQL re-runs the proc in the background without the LIMIT code when it encounters SQL_CALC_FOUND_ROWS? That way there the dev doesn't need to duplicate the code.

My regards

[johnbillion]

Replying to xParham:

Hey @johnbillion, can we also maybe add count as an option for the fields query parameter

Interesting idea! I'll open a follow-up ticket for that so we keep this one focused.

[johnbillion]

Replying to misterwebdeveloper:

Thanks for the comment. I don't think any of this is relevant to this particular ticket, which is about implementing the MySQL change into the WordPress software. Cheers!

[spacedmonkey]

Related: #36881

[rjasdfiii]

• @javorszky - It is a common misconception that one needs to say "COUNT(col)". That goes to the extra effort of counting only rows where "col IS NOT NULL". Unless you want that, you should do "COUNT(*)",

• @johnbillion - "SELECT FOUND_ROWS() query needs to run immediately after the query you're counting the results for, but the corresponding SELECT COUNT(...) does not." I think this is backward. No, even worse. If row(s) where inserted/deleted between the main SELECT and the COUNT, the count could be incorrect -- possibly visibly incorrect. Example: SELECT finds 4 rows; a DELETE intervenes; then COUNT says 3 rows. (OK, maybe no WP users have multiple connections??

• @misterwebdeveloper - Doing the COUNT in the background: Have the web page request the count via AJAX; then fill in the count when the response comes back. That way, the page will run _faster_ than it does today. And you get the count [eventually].

[desrosj]

#55781 was marked as a duplicate.

[johnbillion]

Replying to rjasdfiii:

If row(s) where inserted/deleted between the main SELECT and the COUNT, the count could be incorrect

Thanks for raising this concern. For this problem to occur and cause unexpected or undesired results would require:

1. That a WP_Query is performed
2. That one or more post insertion, post deletion, post update, change to post meta, or change to post term relationship occurs after the SQL query runs but before either of the found_posts or max_num_pages properties are accessed
3. That the above change affects the result of the clauses of the SQL query of the concerned WP_Query
4. If max_num_pages is used, that the resulting change to the found_posts value affects the calculation of max_num_pages (a 1 in 10 chance given 10 posts per page)

There certainly is a window for this opportunity to present itself within a standard request to WordPress (for example it's common for pagination controls in themes to be displayed at the bottom of the page), but it's a small window.

I think the relatively low chance and infrequency of this situation happening, combined with the low severity, is outweighed by the benefit for all other cases where lazy loading the count results in the elimination of the count query when it's not needed.

[spacedmonkey]

Related: #55909

Trac ticket: https://core.trac.wordpress.org/ticket/47280

[spacedmonkey]

I put together a much simpler solution in ​#2829.

[johnbillion]

Cheers @spacedmonkey, are you concerned that the lazy-loading of the max_num_pages and found_posts properties is a risky change? Or are you just proposing a simpler solution to begin with? I think the lazy loading is very close to being complete, I need to find some more time to further test it with plugins that use these properties and related filters.

Count query is not working when using GROUP By in sql query .
and if you remove GROUP By the result is not the same as SELECT FOUND_ROWS()

[akramipro]

COUNT(*) query is not working when using GROUP BY in sql query .
and if you remove GROUP BYthe result is not the same as SELECT FOUND_ROWS()

see this exam. main query result has 415 rows but with count query if we remove GROUP BY we get 423 rows

[akramipro]

After research that I have , I found a solution for getting a count of queries that have GROUP BY .
we can remove GROUP BY and then change count syntax to cover only fields that are used in GROUP BY .
Please see my previous comment and compare it with this image to understand what I say .

[akramipro]

also i wrote this code for count query and it's work like charm:

note: this filter: found_posts_query to set my custom count query and in this filter we can't access $clauses so you need to save $clauses in WP_Query object with this filter : posts_clauses

<?php

/**
 * Fix WordPress Slow Queries
 *
 * @author Mahdi Akrami
 */
class FIX_WP_SLOW_QUERY {

        public static function init() {

                /**
                 * WP_Query
                 */

                add_filter( 'found_posts_query', [ __CLASS__, 'add_found_rows_query' ], 999, 2 );

                add_filter( 'posts_request_ids', [ __CLASS__, 'remove_found_rows_query' ], 999 );

                add_filter( 'posts_pre_query', function ( $posts, \WP_Query $query ) {

                        $query->request = self::remove_found_rows_query( $query->request );

                        return $posts;
                }, 999, 2 );

                add_filter( 'posts_clauses', function ( $clauses, \WP_Query $wp_query ) {

                        $wp_query->fw_clauses = $clauses;

                        return $clauses;
                }, 999, 2 );

        }

        public static function remove_found_rows_query( $sql ) {

                return str_replace( ' SQL_CALC_FOUND_ROWS ', '', $sql );
        }

        public static function add_found_rows_query( $sql, WP_Query $query ) {

                global $wpdb;

                $distinct = $query->fw_clauses['distinct'] ?? '';
                $join     = $query->fw_clauses['join'] ?? '';
                $where    = $query->fw_clauses['where'] ?? '';
                $groupby  = $query->fw_clauses['groupby'] ?? '';

                $count = 'COUNT(*)';

                if ( ! empty( $groupby ) ) {
                        $count = "COUNT( distinct $groupby )";
                }

                return "
                        SELECT $distinct $count
                        FROM {$wpdb->posts} $join
                        WHERE 1=1 $where
                ";
        }

}

FIX_WP_SLOW_QUERY::init();

[rjasdfiii]

@akramipro - It looks like that query could end up with "DISTINCT" twice; this seems wrong:

SELECT DISTINCT COUNT(DISTINCT groupbyvar) ...

Also, it is essentially wrong to mix DISTINCT and GROUP BY:

SELECT DISTINCT ... GROUP BY ...

(Since I don't know what happens after "return", I may be incorrectly critiquing the code.)

[akramipro]

@rjasdfiii - no i think you are wrong . about DISTINCT i tested before and it works fine with no errors . also there is no GROUP BY in my count query . you should read my code with more attention !
Anyway, I tested my code in site with 3.5 million wp_postmeta records and I checked sql logs and all count queries executed with no error and paginations work like before.
Last night my server cpu was 100% but after I fixed the count queries it's under 10% !!

[akramipro]

@rjasdfiii - also i should mention that wordpress add GROUP BY to $groupby after posts_clauses filter so this is safe to use $groupby in my count query $count = "COUNT( distinct $groupby )" since it's just contain group column name.

[rjasdfiii]

Please provide the generated SQL, after the $ variables are interpolated.

[akramipro]

this is the generate sql i send it before !

[rjasdfiii]

Get the 423 rows, look at the 8 rows with duplicate wp_posts.ID. That should explain the purpose of the GROUP BY -- and/or point out redundant or conflicting information in postmeta.

[akramipro]

@rjasdfiii I can't understand you at all. If there is an issue in my code please explain it or give the right solution .
wp default query return 415 rows . ( without my code)
My count query also returns 415 rows.( with my code)
What is the problem here ?

Hi . i write some code to fix wordpress slow query bug in count queries .

i explain the code here: link

<?php

/**
 * Fix WordPress Slow Queries
 *
 * @author Mahdi Akrami
 */
class FIX_WP_SLOW_QUERY {

        public static function init() {

                /**
                 * WP_Query
                 */

                add_filter( 'found_posts_query', [ __CLASS__, 'add_found_rows_query' ], 999, 2 );

                add_filter( 'posts_request_ids', [ __CLASS__, 'remove_found_rows_query' ], 999 );

                add_filter( 'posts_pre_query', function ( $posts, \WP_Query $query ) {

                        $query->request = self::remove_found_rows_query( $query->request );

                        return $posts;
                }, 999, 2 );

                add_filter( 'posts_clauses', function ( $clauses, \WP_Query $wp_query ) {

                        $wp_query->fw_clauses = $clauses;

                        return $clauses;
                }, 999, 2 );

        }

        public static function remove_found_rows_query( $sql ) {

                return str_replace( ' SQL_CALC_FOUND_ROWS ', '', $sql );
        }

        public static function add_found_rows_query( $sql, WP_Query $query ) {

                global $wpdb;

                $distinct = $query->fw_clauses['distinct'] ?? '';
                $join     = $query->fw_clauses['join'] ?? '';
                $where    = $query->fw_clauses['where'] ?? '';
                $groupby  = $query->fw_clauses['groupby'] ?? '';

                $count = 'COUNT(*)';

                if ( ! empty( $groupby ) ) {
                        $count = "COUNT( distinct $groupby )";
                }

                return "
                        SELECT $distinct $count
                        FROM {$wpdb->posts} $join
                        WHERE 1=1 $where
                ";
        }

}

FIX_WP_SLOW_QUERY::init();

[spacedmonkey]

Just flagging that I am currently working on #22176 that will likely end up in 6.1 / 6.2. This adds caching to WP_Query. This affects this patch, because the found_post property is cached. If the value is cached, even in memory, it means that less times it is run.

[johnbillion]

Thinking out loud, if #22176 lands before this then the cached values for found_posts and max_num_pages can also be lazily populated. I'll bear it in mind.

[johnbillion]

@akramipro I've pushed some more commits to ​the pull request which accounts for the GROUP BY clause using a field name or names other than wp_posts.ID. Thanks.

@rjasdfiii The point that @akramipro was trying to make is that if the original query contains a GROUP BY clause then the subsequent COUNT(...) query needs to contain a DISTINCT clause in order to account for rows that were grouped in the main query. This can be seen when performing a meta query with the relation field set to OR and a result set that contains posts that match both the meta clauses. Without the COUNT(DISTINCT ...) clause the count will not be correct.

[spacedmonkey]

Now that #22176 is committed in [54111], this patch will need to be refactored.

[archon810]

Think we can expect to see this one finished and released early next year? It's going to result in huge perf improvements.

[JaworskiMatt]

This is a very important improvement no matter what MySQL version the website is using. Huge databases end up being extremely slow because of this, and in case of our plugin WordPress insists on counting the rows in our WP_Queryies even when we don't need it for anything, leading to major performance degradation. Trying to circumvent it with filters has never worked fine.

We hope this makes it into Core sooner than later.

Trac ticket: https://core.trac.wordpress.org/ticket/47280

I'm going to close this off in favour of #3863 which carries on this work. Cheers!

Closing this in favour of #3863

[johnbillion]

I've opened a new PR for this: ​https://github.com/WordPress/wordpress-develop/pull/3863

Lazy loading the found_posts and max_num_pages properties was an interesting exercise but it introduces a few potential problems, not least potentially inaccurate counts if a post in the result set is updated in between the query being run and one of those properties being accessed.

The new PR scales back the changes to just the replacement of SQL_COUNT_FOUND_ROWS and SELECT FOUND_ROWS() with a performant COUNT query that's the approach recommended by MySQL. It includes feedback from the discussion on this ticket and the original PRs.

New tests have been added and some existing tests have been updated.

There is one outstanding issue which is to decide how to handle a change to the query via the posts_request filter (the only filter that runs between the query being constructed and executed) which could potentially result in an inaccurate count. I don't think there's a way to avoid this while still removing SQL_COUNT_FOUND_ROWS.

Testing and feedback welcome and appreciated.