[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):

 Replying to [comment:27 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.

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