[wp-trac] [WordPress Trac] #37612: Use blog_id field while selecting blogs for networks upgrade

[WordPress Trac]

#37612: Use blog_id field while selecting blogs for networks upgrade
--------------------------------+-----------------------------
 Reporter:  fliespl             |      Owner:
     Type:  enhancement         |     Status:  new
 Priority:  normal              |  Milestone:  Awaiting Review
Component:  Networks and Sites  |    Version:  trunk
 Severity:  normal              |   Keywords:
  Focuses:  multisite           |
--------------------------------+-----------------------------
 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.)

--
Ticket URL: <https://core.trac.wordpress.org/ticket/37612>
WordPress Trac <https://core.trac.wordpress.org/>
WordPress publishing platform