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 🙂