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

 * @javorszky - It is a common misconception that one needs to say
 "COUNT(col)".  That goes to the extra effort of counting only rows where
 "col IS NOT NULL".  Unless you want that, you should do "COUNT(*)",

 * @johnbillion - "SELECT FOUND_ROWS() query needs to run immediately after
 the query you're counting the results for, but the corresponding SELECT
 COUNT(...) does not."  I think this is backward.  No, even worse.  If
 row(s) where inserted/deleted between the main SELECT and the COUNT, the
 count could be incorrect -- possibly visibly incorrect.  Example: SELECT
 finds 4 rows; a DELETE intervenes; then COUNT says 3 rows.  (OK, maybe no
 WP users have multiple connections??

 * @misterwebdeveloper - Doing the COUNT in the background:  Have the web
 page request the count via AJAX; then fill in the count when the response
 comes back.  That way, the page will run _faster_ than it does today.  And
 you get the count [eventually].

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