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

Comment (by kunalkataria):

 Replying to [comment:68 johnbillion]:
 > Unfortunately there's been no progress on this since
 https://core.trac.wordpress.org/ticket/47280#comment:59 . We need some
 ideas on how to deal with the `posts_request` filter.

 According to this [https://dev.mysql.com/worklog/task/?id=12615 MySQL
 task], `SQL_CALC_FOUND_ROWS` is significantly faster than `count` in
 almost all cases.

 Though in one case `SQL_CALC_FOUND_ROWS` can be faster, when searching in
 non-indexed column without any `LIMIT` value with large number of rows.
 Here, there will two roundtrips for both `count` and `select` query.

 But fetching these many results in a single go is never recommended.


 SOL I : Rely on plugin developers that they will find this very rare edge
 case which will actually be more performant and not memory intensive for
 php instance. Ensure that installed mysql version supports this query and
 then allow it to be executed. Also, produce warning to ensure developer
 knows about this.

 SOL II: Explicitly remove `SQL_CALC_FOUND_ROWS` from query and warn the
 developers that they can't use this in `WP_Query`, they can use `wpdb`
 instance for using this deprecated query.

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