WordPress.org

Make WordPress Core

Opened 3 years ago

Closed 3 years ago

Last modified 3 years ago

#37612 closed enhancement (fixed)

Use blog_id field while selecting blogs for networks upgrade

Reported by: fliespl Owned by: pento
Milestone: 4.7 Priority: normal
Severity: normal Version:
Component: Networks and Sites Keywords:
Focuses: multisite, performance Cc:

Description

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.)

Change History (5)

#2 @ocean90
3 years ago

  • Focuses performance added

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

#3 @pento
3 years ago

  • Milestone changed from Awaiting Review to 4.7
  • Owner set to pento
  • Status changed from new to assigned
  • Version trunk deleted

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.

#4 @pento
3 years ago

  • Resolution set to fixed
  • Status changed from assigned to closed

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.

#5 @johnjamesjacoby
3 years ago

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

Note: See TracTickets for help on using tickets.