[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:  performance
--------------------------------------------+-----------------------------

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

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