slow sql query from probably is_blog_installed

[mark-k]

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.

[ryan]

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.

[mark-k]

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

[wpmuguru]

check for just this blog's tables

[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")

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

[wpmuguru]

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?

[nacin]

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.

[nacin]

Somewhere along the line we stopped querying SHOW TABLES.