Yesterday I made a terrible mistake by updating Yoast SEO plugin to its latest version (8.3) without prior testing.

This was caused by queries like this:

SELECT post_id FROM wp_postmeta WHERE meta_key = '_wp_attached_file' AND meta_value = 'https://server.cdn/path/to/image.jpg';

EXPLAIN SELECT showed:

+------+-------------+-------------+------+---------------+----------+---------+-------+---------+-------------+
| id   | select_type | table       | type | possible_keys | key      | key_len | ref   | rows    | Extra       |
+------+-------------+-------------+------+---------------+----------+---------+-------+---------+-------------+
|    1 | SIMPLE      | wp_postmeta | ref  | meta_key      | meta_key | 767     | const | 1313736 | Using where |
+------+-------------+-------------+------+---------------+----------+---------+-------+---------+-------------+

The issue is that we have millions of images, and Yoast SEO for some reason needed the ID of the post with the image.

I added define('SAVEQUERIES', true); to wp-config.php and this snippet to the theme footer:

global $wpdb;
foreach ($wpdb->queries as $x) {
    echo $x[1], "\t", $x[2], "\t", htmlspecialchars($x[0]), "\n";
}

SELECT query took 3 seconds to complete, and I was able to track it down to Yoast SEO:

wp_head, do_action('wp_head'), WP_Hook->do_action, WP_Hook->apply_filters, WPSEO_Frontend->head, do_action('wpseo_head'), WP_Hook->do_action, WP_Hook->apply_filters, WPSEO_OpenGraph->opengraph, do_action('wpseo_opengraph'), WP_Hook->do_action, WP_Hook->apply_filters, WPSEO_OpenGraph->image, WPSEO_OpenGraph_Image->__construct, WPSEO_OpenGraph_Image->set_images, WPSEO_OpenGraph_Image->set_singular_image, WPSEO_OpenGraph_Image->add_first_usable_content_image, WPSEO_Image_Utils::get_attachment_by_url, WPSEO_Image_Utils::attachment_url_to_postid, attachment_url_to_postid

The quick and dirty solution (except for downgrading Yoast SEO) was to add another index to wp_postmeta:

ALTER TABLE wp_postmeta ADD KEY meta_value(meta_value(128));

The things got better:

+------+-------------+-------------+------+---------------------+------------+---------+-------+------+-------------+
| id   | select_type | table       | type | possible_keys       | key        | key_len | ref   | rows | Extra       |
+------+-------------+-------------+------+---------------------+------------+---------+-------+------+-------------+
|    1 | SIMPLE      | wp_postmeta | ref  | meta_key,meta_value | meta_value | 515     | const |    1 | Using where |
+------+-------------+-------------+------+---------------------+------------+---------+-------+------+-------------+

The moral of the story: test before you deploy 🙂

Newer Is Not Necessarily Better: Updated Yoast SEO to 8.3
Tagged on:         

Leave a Reply

Your email address will not be published.