WordPress.org

Make WordPress Core

Opened 5 years ago

Closed 18 months 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 5 years ago.
check for just this blog's tables

Download all attachments as: .zip

Change History (9)

comment:1 @ryan5 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.

comment:2 @mark-k5 years ago

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

@wpmuguru5 years ago

check for just this blog's tables

comment:3 @wpmuguru5 years ago

  • Keywords has-patch added

comment:4 follow-ups: @jimmcq5 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

comment:5 in reply to: ↑ 4 @wpmuguru5 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?

comment:6 in reply to: ↑ 4 @nacin5 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.

comment:7 @nacin5 years ago

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

comment:8 @nacin18 months 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.