Use blog_id field while selecting blogs for networks upgrade

[fliespl]

Currently query uses ORDER BY registered DESC which is inefficient on large networks (column without index). In most cases registered is in the same order as blog_id. Rewriting this query to order by blog_id causes significant performance increase.

SELECT SQL_NO_CACHE * FROM wp_blogs WHERE site_id = '1' AND spam = '0' AND deleted = '0' AND archived = '0' ORDER BY registered DESC LIMIT 25000, 5

Showing rows 25000 - 25004 (5 total, Query took 11.3723 seconds.)

SELECT SQL_NO_CACHE * FROM wp_blogs WHERE site_id = '1' AND spam = '0' AND deleted = '0' AND archived = '0' ORDER BY blog_id DESC LIMIT 25000, 5

Showing rows 25000 - 25004 (5 total, Query took 0.0314 seconds.)

Further rows:
registered

• Showing rows 500000 - 500004 (5 total, Query took 19.1260 seconds.)
• Showing rows 1500000 - 1500004 (5 total, Query took 8.7060 seconds.)

blog_id

• Showing rows 500000 - 500004 (5 total, Query took 0.3587 seconds.)
• Showing rows 1500000 - 1500004 (5 total, Query took 1.0150 seconds.)

[fliespl]

Pull request created ​https://github.com/WordPress/WordPress/pull/225

[ocean90]

Introduced in ​mu278, changed in ​mu528 and [30029].

[pento]

Nice find, @fliespl!

The upgrade script just needs to order by something that won't change, blog_id will do that job just as well as registered.

[pento]

In 38229:

Multisite: Improve performance of the upgrade page on large networks.

The query to select the next 5 blogs to upgrade was ordered by registered, which isn't indexed. This causes the query to table scan, which will be slow on networks with many blogs.

The query only needs to be ordered by something that won't change, so ordering by blog_id is a good replacement. blog_id is indexed, and it's the only column being returned, so MySQL is able to optimize for a fast index read.

Props fliespl.
Fixes #37612.

[johnjamesjacoby]

I just leveraged this on a few thousand sites. Nice work. <3