[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    |     Focuses:  performance
-------------------------+-----------------------------
Changes (by johnbillion):

 * owner:  (none) => johnbillion
 * status:  new => reviewing


Comment:

 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.

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

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