[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:  Future Release
Component:  Database     |     Version:
 Severity:  normal       |  Resolution:
 Keywords:  needs-patch  |     Focuses:
-------------------------+-----------------------------

Comment (by morgantocker):

 Former MySQL Product Manager here. Just a reply to this question
 specifically:

 > does the replacment `SELECT COUNT(*) WHERE ...` method recommended in
 the MySQL docs have a performance advantage?

 Yes it can. What happens with `SQL_CALC_FOUND_ROWS` is that it disables
 the optimizations that MySQL can apply when there is a `LIMIT`.

 The corresponding `COUNT(*)` query will have its own optimizations
 available too, such as covering indexes.

 So the problem with `SQL_CALC_FOUND_ROWS` is you kind of get the worst of
 both worlds - with neither types of optimizations applying. Issuing two
 queries means one more network roundtrip, but on the MySQL-side both of
 the queries will be more efficient.

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