[wp-trac] [WordPress Trac] #47280: SQL_CALC_FOUND_ROWS is deprecated as of MySQL 8.0.17

[WordPress Trac]

#47280: SQL_CALC_FOUND_ROWS is deprecated as of MySQL 8.0.17
-------------------------------------------------+-------------------------
 Reporter:  javorszky                            |       Owner:
                                                 |  johnbillion
     Type:  enhancement                          |      Status:  reviewing
 Priority:  normal                               |   Milestone:  Future
                                                 |  Release
Component:  Database                             |     Version:
 Severity:  normal                               |  Resolution:
 Keywords:  has-patch has-unit-tests early       |     Focuses:
  changes-requested                              |  performance
-------------------------------------------------+-------------------------

Comment (by spacedmonkey):

 I posted some quick benchmarks [https://github.com/WordPress/wordpress-
 develop/pull/3863#issuecomment-1640491911 here].


 > The posts_request filter is a real stumbling block for this change. The
 SQL to perform the count query can be reliably adjusted to work without
 SQL_COUNT_FOUND_ROWS, but the posts_request filter runs between the query
 being constructed and before it runs, which means arbitrary changes to the
 query can be made in a filter and cause the count query to be incorrect.
 We'll need some suggestions for how to solve this.

 It is worth noting that is the same problem with the filter
 `posts_request_ids`.

 Maybe the best we can do is detect if posts_request / posts_request_ids
 have a filter hooked in and if do, fallback to the old style
 `SQL_CALC_FOUND_ROWS` queries. This way, string replacement would work
 within filters. We could add a `_doing_it_wrong` and write a dev note to
 ensure developers update their code, when it falls back. Any clever
 manipulation of query after it has been filter may result in an invalid
 count query. Worse, we may not know it is invalid and count could just be
 wrong.

 I think we should be pushing developers to use the `posts_pre_query` if
 they want to run custom queries or change the value WP_Query returns.

-- 
Ticket URL: <https://core.trac.wordpress.org/ticket/47280#comment:60>
WordPress Trac <https://core.trac.wordpress.org/>
WordPress publishing platform