WordPress.org

Make WordPress Core

Opened 15 months ago

Last modified 2 months ago

#47280 new enhancement

SQL_CALC_FOUND_ROWS is deprecated as of MySQL 8.0.17

Reported by: javorszky Owned by:
Milestone: Future Release Priority: normal
Severity: normal Version:
Component: Database Keywords: has-patch
Focuses: Cc:

Description

Per https://dev.mysql.com/doc/refman/8.0/en/information-functions.html#function_found-rows

The SQL_CALC_FOUND_ROWS query modifier and accompanying FOUND_ROWS() function are deprecated as of MySQL 8.0.17 and will be removed in a future MySQL version. As a replacement, considering executing your query with LIMIT, and then a second query with COUNT(*) and without LIMIT to determine whether there are additional rows.

This is not yet immediately important because most hosts are on 5.5, or 5.6, rarely 5.7, but given the speed with which trac tickets move that impact very core functionalities, I thought it best to open this ticket to get the work started.

This impacts all the 6 places where it's being used, though one of them is in the WP_Query definition.

Change History (5)

#1 @johnbillion
15 months ago

  • Keywords needs-patch added
  • Milestone changed from Awaiting Review to Future Release
  • Severity changed from minor to normal
  • Version trunk deleted

#2 follow-up: @johnbillion
10 months ago

@javorszky Is this something you're willing to work on? As SQL_CALC_FOUND_ROWS can get slow for a large data set, does the replacment SELECT COUNT(*) WHERE ... method recommended in the MySQL docs have a performance advantage?

#3 @javorszky
10 months ago

No. For various reasons I have stepped back from contributing to WordPress in any way except for this ticket via comments.

Regarding the performance advantage, as long as the select count(column) from table where ... happens on indices, and uses only one column, it should be okay.

Also per mysql docs, there's not much in terms of alternatives.

Alternative is to not have the number of total rows available any more, which I suspect would break bc.

#4 in reply to: ↑ 2 @morgantocker
2 months ago

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.

This ticket was mentioned in PR #330 on WordPress/wordpress-develop by morgo.


2 months ago

  • Keywords has-patch added; needs-patch removed

Signed-off-by: Morgan Tocker <tocker@…>

Trac ticket: https://core.trac.wordpress.org/ticket/47280

Note: See TracTickets for help on using tickets.