Database Schema Optimizations

[Denis-de-Bernardy]

Diffs coming...

[Denis-de-Bernardy]

9642-upgrade.php.diff gets rid of multitudes of pointless upgrade queries that can be done in one go. they're not used often, since it's old stuff, but I bumped into it while looking into posts-related queries...

[Denis-de-Bernardy]

that last patch reveals quite a bit about existing WP queries :-)

[Denis-de-Bernardy]

useful debugging code, after the require in wp-config.php:

function dump_queries() {
	global $wpdb;
	foreach ( $wpdb->queries as $query ) {
		echo '<pre style="text-align: left; padding: 10px; border: solid 1px #000;">' . "\n";
		var_dump($query);
		echo '</pre>' . "\n";
	}
}

if ( defined('SAVEQUERIES') && defined('QUERIES_DEBUG') ) {
	add_action('wp_footer', 'dump_queries');
	add_action('admin_footer', 'dump_queries');
}

[Denis-de-Bernardy]

db debug tools (updated)

[DD32]

Correct me if i'm wrong, But THEN is a MySQL 5 thing is it not?

[Denis-de-Bernardy]

no no... case when foo then bar when foo then bar else bar end is ansi.

[Denis-de-Bernardy]

*grin*. This is sooo much better:

mysql> explain select www_posts.* FROM www_posts  WHERE 1=1  AND www_posts.post_type = 'post' AND (www_posts.post_status = 'publish' OR www_posts.post_status = 'private')  ORDER BY www_posts.post_date_gmt DESC LIMIT 0, 10 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: www_posts
         type: ref
possible_keys: type_parent,type_date_gmt,type_modified_gmt
          key: type_date_gmt
      key_len: 62
          ref: const
         rows: 304
        Extra: Using where
1 row in set (0.01 sec)

[Denis-de-Bernardy]

The most horrible to optimize are the page related queries. MySQL's optimizer is stubbornly refuses to use an index on multi-field sorts such as menu_order, post_title ASC...

[Denis-de-Bernardy]

oh silly me, nevermind that, there is no limit. no wonder it's file-sorting. :D

[DD32]

case when foo then bar when foo then bar else bar end is ansi.

Ah... The docs suggest its MySQL5+ only.. but it is in 4.. Interesting SQL i've never come across before :)

[Denis-de-Bernardy]

this is not finished yet

[Denis-de-Bernardy]

Related: #8885

[Denis-de-Bernardy]

See also: #9422

[Denis-de-Bernardy]

Related: #7415

[dd32]

I was nearly going to query the need for the upgrade patch from here.. ("Why bother touching the old upgrade code.. its working and not used by new installs") But given thats for the 2.0 -> onwards upgrade.. I think that might be a worthy inclusion.

Certainly going to be a lot more efficient than the current loop which could end up with a large number of queries being made.

[dbuser123]

Posted before but still not fixed.

Another slow query (0.30s for a 50 MB comments table) I regularly encounter is this one:

SELECT comment_post_ID
FROM comments
WHERE LCASE(comment_author_email) = 'user@host.com' AND comment_subscribe='Y' AND comment_approved = '1'
GROUP BY comment_post_ID

It would be better to always store e-mail as lowercase, stop using the LCASE function, and add an index on (comment_author_email, comment_post_ID).

[dbuser123]

Why is autoload in the options table not an enum field?

[Denis-de-Bernardy]

Replying to dbuser123:

Why is autoload in the options table not an enum field?

For legacy reasons and/or far away compatibility with other DB engines?

Else, agreed. It should be a tinyint(1).

D.

[dd32]

Posted before but still not fixed.

Probably because its not a query WordPress makes.

I believe that belongs to the Subscribe to comments plugin

[dbuser123]

Replying to dd32:

Probably because its not a query WordPress makes.

I see, sorry for the disturbance.

A minor improvement for the comments table: the first index is redundant since it is a prefix of the second one.
KEY comment_approved (comment_approved),
KEY comment_approved_date_gmt (comment_approved,comment_date_gmt),

[hakre]

Reviewed the ticket again. Last patch has the comment this is not finished yet. Looks like something is still missing here?

[Denis-de-Bernardy]

Yeah, there are tons of potential optimizations. Low hanging fruits, too. But this ought better be revisited after the merge, rather than at the same time. The merge is going to make a load of patches stale.

[Otto42]

Replying to dbuser123:

Posted before but still not fixed.

Another slow query (0.30s for a 50 MB comments table) I regularly encounter is this one:

SELECT comment_post_ID
FROM comments
WHERE LCASE(comment_author_email) = 'user@host.com' AND comment_subscribe='Y' AND comment_approved = '1'
GROUP BY comment_post_ID

It would be better to always store e-mail as lowercase, stop using the LCASE function, and add an index on (comment_author_email, comment_post_ID).

Old, but it needs to be said: If we're doing this somewhere, then we're doing it wrong.

Email addresses are case sensitive. The local part, anyway. See RFC 2821: ​http://www.faqs.org/rfcs/rfc2821.html

The local-part of a mailbox
MUST BE treated as case sensitive. Therefore, SMTP implementations
MUST take care to preserve the case of mailbox local-parts. Mailbox
domains are not case sensitive. In particular, for some hosts the
user "smith" is different from the user "Smith". However, exploiting
the case sensitivity of mailbox local-parts impedes interoperability
and is discouraged.

While most all systems nowadays are case insensitive, technically in the first part of the email address, case matters. So we shouldn't be using LCASE here at all, or lowercasing email addresses.

[nacin]

I don't know of a system that doesn't treat emails as case insensitive. Granted, the RFC says that, but it doesn't apply to the domain, and local servers pretty much always handle the the both. I'll call it the Facebook effect -- needing to play to the lowest common denominator of users.

I agree with killing the LCASE, if we can standardize on lowercase on storage. There's another ticket somewhere (from tellyworth, re: Akismet) asking for the index.

[westi]

Replying to dbuser123:

Replying to dd32:

Probably because its not a query WordPress makes.

I see, sorry for the disturbance.

A minor improvement for the comments table: the first index is redundant since it is a prefix of the second one.
KEY comment_approved (comment_approved),
KEY comment_approved_date_gmt (comment_approved,comment_date_gmt),

This is lost and buried in this very generic ticket so I've raised a new one #19935

[chriscct7]

Needs to be finished, and what is already there needs to be refreshed (baring in mind some of it was merged in #19935)

[pento]

This ticket is wildly outdated.

If someone feels like taking another stab at it, they're welcome to, but tackling individual optimisations as they come up in individual tickets seems to have made more progress over the years.