[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:  major                     |  Resolution:
 Keywords:  has-patch has-unit-tests  |     Focuses:  performance
--------------------------------------+-----------------------------

Comment (by 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.

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