[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:  (none)
     Type:  enhancement  |      Status:  new
 Priority:  normal       |   Milestone:  5.7
Component:  Database     |     Version:
 Severity:  normal       |  Resolution:
 Keywords:  has-patch    |     Focuses:
-------------------------+---------------------

Comment (by morgantocker):

 @wpe_bdurette I started a reply before seeing your edit. But I will
 include it here since it might still be useful.

 Consider this example:


 {{{
 CREATE TABLE t1 (id INT NOT NULL);
 INSERT INTO t1 VALUES (1),(2),(3),(4),(5);
 SELECT * FROM t1 LIMIT 3;
 }}}




 {{{
 mysql> SELECT COUNT(*) FROM (SELECT * FROM t1 LIMIT 3) p; # applies LIMIT,
 returns incomplete total
 +----------+
 | COUNT(*) |
 +----------+
 |        3 |
 +----------+
 1 row in set (0.00 sec)


 mysql> SELECT COUNT(*) FROM (SELECT * FROM t1) p; # correct
 +----------+
 | COUNT(*) |
 +----------+
 |        5 |
 +----------+
 1 row in set (0.00 sec)
 }}}



 In terms of efficiency:

 In MySQL 5.7+ it is just as efficient as rewriting the query to not use
 the subquery. You should be able to see this in explain:


 {{{
 mysql [localhost:8022] {msandbox} (test) > explain SELECT COUNT(*) FROM
 (SELECT * FROM t1) p;
 +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
 | id | select_type | table | partitions | type | possible_keys | key  |
 key_len | ref  | rows | filtered | Extra |
 +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
 |  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL |
 NULL    | NULL |    5 |   100.00 | NULL  |
 +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
 1 row in set, 1 warning (0.00 sec)

 mysql [localhost:8022] {msandbox} (test) > show warnings;
 +-------+------+---------------------------------------------------------------+
 | Level | Code | Message
 |
 +-------+------+---------------------------------------------------------------+
 | Note  | 1003 | /* select#1 */ select count(0) AS `COUNT(*)` from
 `test`.`t1` |
 +-------+------+---------------------------------------------------------------+
 1 row in set (0.00 sec)

 }}}


 The optimization that rewrites subqueries in the from clause is called
 "derived_merge". It is a MySQL 5.7+ feature, and enabled by default.
 Earlier versions will be less efficient:


 {{{
 mysql [localhost:8022] {msandbox} (test) > set
 optimizer_switch="derived_merge=off";
 Query OK, 0 rows affected (0.00 sec)

 mysql [localhost:8022] {msandbox} (test) > explain SELECT COUNT(*) FROM
 (SELECT * FROM t1) p;
 +----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------+
 | id | select_type | table      | partitions | type | possible_keys | key
 | key_len | ref  | rows | filtered | Extra |
 +----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------+
 |  1 | PRIMARY     | <derived2> | NULL       | ALL  | NULL          | NULL
 | NULL    | NULL |    5 |   100.00 | NULL  |
 |  2 | DERIVED     | t1         | NULL       | ALL  | NULL          | NULL
 | NULL    | NULL |    5 |   100.00 | NULL  |
 +----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------+
 2 rows in set, 1 warning (0.00 sec)

 mysql [localhost:8022] {msandbox} (test) > show warnings;
 +-------+------+-------------------------------------------------------------------------------------------------------------------------+
 | Level | Code | Message
 |
 +-------+------+-------------------------------------------------------------------------------------------------------------------------+
 | Note  | 1003 | /* select#1 */ select count(0) AS `COUNT(*)` from (/*
 select#2 */ select `test`.`t1`.`id` AS `id` from `test`.`t1`) `p` |
 +-------+------+-------------------------------------------------------------------------------------------------------------------------+
 1 row in set (0.00 sec)
 }}}

 I don't think the worse performance in 5.6 and lower will be that
 impactful for the typical sized database, but I will let others judge that
 one. There is technically a difference.

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