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

 Replying to [comment:16 pembo13]:
 > Is this being actively worked on? Luckily, it doesn't look like MariaDB
 has deprecated this as well.

 Regarding `SQL_CALC_FOUND_ROWS` vs. `COUNT(*)` in MariaDB, there is
 confusing documentation on a small point that concerned me until I tested
 it. While there's no indication of deprecation in the docs, we might as
 well clear up this small point for anyone else.

 * The MariaDB doc https://mariadb.com/kb/en/found_rows/ says "Statements
 which don't return any results don't affect FOUND_ROWS() - the previous
 value will still be returned."

 * The doc probably means "Statements which don't return any results
 ''**still do**'' affect FOUND_ROWS() - the previous ''**(valid) statement
 is always the basis of the value**'' returned."

 * Here's why I think so. In my testing I ran code with, first, a
 `$wpdb->get_results( 'SELECT SQL_CALC_FOUND_ROWS ...' )` query with non-
 empty results, followed by `$wpdb->get_var( 'SELECT FOUND_ROWS()' )`. In
 the same code, second, a different `$wpdb->( 'SELECT SQL_CALC_FOUND_ROWS
 ...')` with empty results (due to `LIMIT n, m`), followed by
 `$wpdb->get_var( 'SELECT FOUND_ROWS()' )`. The second `FOUND_ROWS()` was
 correct for the second query, different from the first.

 * Or, `$wpdb` does some magic to fix it. Tested on the current WP release,
 6.7.1, with MariaDB 5.5.5-10.2.36-MariaDB-log. I did not use `WP_Query`.

 * The PHPMyAdmin issue, resolved and committed,
 https://github.com/phpmyadmin/phpmyadmin/issues/15652 removes
 `SQL_CALC_FOUND_ROWS`. That issue mentions this WP ticket, regarding non-
 deprecation in MariaDB!

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