WordPress.org

Make WordPress Core

Opened 6 years ago

Closed 2 years ago

#14397 closed defect (bug) (invalid)

slow sql query from probably is_blog_installed

Reported by: mark-k Owned by:
Milestone: Priority: normal
Severity: normal Version: 3.0
Component: Performance Keywords: has-patch 2nd-opinion
Focuses: Cc:

Description

When everything else fails, is_blog_installed uses
$tables = $wpdb->get_col('SHOW TABLES');
which can be costly in a big multisite or even when you have many single installations on the same DB

Related question - why does is_blog_installed is even being called on wp_widgets_init()? I would assume that if the DB is not set properly not only the widgets will suffer but also the themes and the plugins which are not protected at the same way.

Attachments (1)

14397.diff (603 bytes) - added by wpmuguru 6 years ago.
check for just this blog's tables

Download all attachments as: .zip

Change History (9)

#1 @ryan
6 years ago

The check in wp_widgets_init() prevents widgets from trying to write options if the DB isn't set up yet. Typically during initial install there are no active plugins and the only theme is the default theme.

We need to be able to detect the presence of any of the blog's tables, even if they are empty. Alternatives to SHOW TABLES welcome.

#2 @mark-k
6 years ago

I don't know enough about mysql optimization, but could it be that "show tables like $blog_prefix%" is faster?

@wpmuguru
6 years ago

check for just this blog's tables

#3 @wpmuguru
6 years ago

  • Keywords has-patch added

#4 follow-ups: @jimmcq
5 years ago

In my tests with a large number of blogs, "SHOW TABLES LIKE '{$blog_prefix}%'" was still pretty slow (30 seconds as opposed to 2 minutes for a generic "SHOW TABLES")

It seems a little backwards to first get the list of all tables and then see if a query exists... wouldn't it make more sense to iterate through the list of queries and then see if the table for each query exists?

A query like "SELECT COUNT(*) AS count FROM information_schema.tables WHERE table_schema = '$database' AND table_name = '$tablename'" on each table seemed to be much faster, but information_schema is only supported in MySQL 5, so it may have to wait for WordPress 3.2

#5 in reply to: ↑ 4 @wpmuguru
5 years ago

Replying to jimmcq:

A query like "SELECT COUNT(*) AS count FROM information_schema.tables WHERE table_schema = '$database' AND table_name = '$tablename'"

I don't think we can rely on every install having access to information_schema.

@Ryan - Would "SHOW CREATE TABLE $wpdb->options" and iterate through the nine tables do the trick?

#6 in reply to: ↑ 4 @nacin
5 years ago

Replying to jimmcq:

In my tests with a large number of blogs, "SHOW TABLES LIKE '{$blog_prefix}%'" was still pretty slow (30 seconds as opposed to 2 minutes for a generic "SHOW TABLES")

We could probably leverage wpdb::base_prefix here.

Not sure how much faster the LIKE would really be, though. In is_blog_installed(), chances are we're going to die because wpdb::site doesn'r exist, right? Maybe we can make that a much more direct query for global tables, before we look for blog-level tables.

#7 @nacin
5 years ago

  • Keywords 2nd-opinion added
  • Milestone changed from Awaiting Review to Future Release

#8 @nacin
2 years ago

  • Milestone Future Release deleted
  • Resolution set to invalid
  • Status changed from new to closed

Somewhere along the line we stopped querying SHOW TABLES.

Note: See TracTickets for help on using tickets.