[wp-trac] [WordPress Trac] #17998: New Site & Upgrade process uses unrestricted "show all" causing timeouts

[WordPress Trac]

#17998: New Site & Upgrade process uses unrestricted "show all" causing timeouts
--------------------------+-----------------------------
 Reporter:  brianlayman   |      Owner:
     Type:  defect (bug)  |     Status:  new
 Priority:  normal        |  Milestone:  Awaiting Review
Component:  Database      |    Version:  3.2
 Severity:  major         |   Keywords:
--------------------------+-----------------------------
 The dbdelta function does a straight "show tables" which is not scalable
 to large multisite installs.

 The routine is built to look at and upgrade or create all of the tables in
 the 'global' table list (blogs, sitemeta & etc) as well as tables for the
 individual site being upgraded (comments, options & etc). In the current
 version, it asks MySQL to list all of the tables it knows and then
 iterates that entire list picking out just those tables listed above.  On
 large sites, this simple "show tables" request can take 5+ minutes to
 complete.

 This patch uses the standard MySQL show tables call with a where clause to
 limit the query to the just the global tables and the tables that start
 with the prefix for the site we are looking at.

 It should be fully compatible with both standalone and MS WordPress
 installations.

 It requires MySQL 5.0 and therefore will only work with WordPress 3.2+

 I use this in the field with two independent clients each with more than
 10,000 sites in their MS network.  With over 90,000 tables, the one site
 would have taken 8 days to complete its upgrade.  With this patch, it took
 less than 4 hours to complete the upgrade.  The other network was timing
 out with every new user that signed up.  This caused inconsistent issues
 ranging from tables never being created for the blogs to the default
 themes and settings never being applied. Sometimes, if another blog was
 created right away, a blog creation would succeed. I think this was when
 the query result was still cached by the MySQL server.

-- 
Ticket URL: <http://core.trac.wordpress.org/ticket/17998>
WordPress Trac <http://core.trac.wordpress.org/>
WordPress blogging software