[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 has-unit-tests  |     Focuses:  performance
--------------------------------------+-----------------------------

Comment (by misterwebdeveloper):

 Hi,

 Experienced software developer of 20 years here. I just want to start by
 saying MySQL is a crazy good open source platform and kudos to everyone
 involved building it.

 Sorry if I'm posting this in the wrong place...

 Let me play devil's advocate.

 As far as I can see the recommendation is to break DRY and duplicate
 SELECT statements.
 Presumably this feature was orignally added to prevent that.

 The MYSQL recommendation: duplicate the original query. So a SQL statement
 using SQL_CALC_FOUND_ROWS is 150 lines, most of those lines should be
 duplicated? This isn't a pragmatic solution. What if an existing system is
 using 50k lines? Is there something I'm missing here?

 Another suggestion is to rewrite systems to use lazy loading. Not all
 systems are lazily loaded social media platforms. Pagination still has
 it's place and isn't going anywhere long term.

 Is it possible to fix/improve the performance issues in this feature? How
 about MySQL re-runs the proc in the background without the LIMIT code when
 it encounters SQL_CALC_FOUND_ROWS? That way there the dev doesn't need to
 duplicate the code.

 My regards

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