[Bug]: Extremely slow post editing due to missing index in keyword_usage function #22933
Description
Prerequisites
- I've read and understood the contribution guidelines.
- I've searched for any related issues and avoided creating a duplicate issue.
Please give us a description of what happened
Opening a post for editing on large instances (e.g. /wp-admin/post.php?post=xyz&action=edit) takes more than 10 seconds.
The slowdown is caused by a missing database index used by the keyword_usage function in inc/class-wpseo-meta.php at line 971.
$post_ids = $repository->query()
->select( 'object_id' )
->where( 'primary_focus_keyword', $keyword )
->where( 'object_type', 'post' )
->where_not_equal( 'object_id', $post_id )
->where_not_equal( 'post_status', 'trash' )
->limit( 2 ) // Limit to 2 results to save time and resources.
->find_array();
The fix is to add appropriate index:
ALTER TABLE `wp_yoast_indexable` ADD INDEX `primary_focus_keyword_and_object_type` (`primary_focus_keyword`, `object_type`, `post_status`, `object_id`);
Profiler shows:
{
"human_time": "3.083564", // seconds
"memory": 947304,
"file": "/home/wordpress/wp-content/plugins/wordpress-seo/admin/metabox/class-metabox.php",
"line": 805,
"function": "WPSEO_Metabox::enqueue"
},
{
"human_time": "3.076714", // seconds
"memory": 721992,
"file": "/home/wordpress/wp-content/plugins/wordpress-seo/admin/metabox/class-metabox.php",
"line": 805,
"function": "WPSEO_Video_Metabox::enqueue"
},
{
"hook": "wpseo_posts_for_related_keywords",
"human_time": "1.536928", // seconds
"memory": 70336,
"functions": [
{
"human_time": "1.536904",
"memory": 68064,
"file": "/home/wordpress/wp-content/plugins/wordpress-seo-premium/src/integrations/admin/keyword-integration.php",
"line": 38,
"function": "Yoast\WP\SEO\Premium\Integrations\Admin\Keyword_Integration::add_posts_for_related_keywords"
}
],
}
{
"hook": "wpseo_posts_for_related_keywords",
"human_time": "1.516784", // seconds
"memory": 72552,
"functions": [
{
"human_time": "1.516768",
"memory": 70560,
"file": "/home/wordpress/wp-content/plugins/wordpress-seo-premium/src/integrations/admin/keyword-integration.php",
"line": 38,
"function": "Yoast\WP\SEO\Premium\Integrations\Admin\Keyword_Integration::add_posts_for_related_keywords"
}
],
}
Explanation:
SELECT COUNT(*) FROM `wp_yoast_indexable`;
+----------+
| COUNT(*) |
+----------+
| 813372 |
+----------+
1 row in set (0.104 sec)
SELECT SQL_NO_CACHE `object_id` FROM `wp_yoast_indexable` WHERE `primary_focus_keyword` = 'test' AND `object_type` = 'post' AND `object_id` != '2952125' AND `post_status` != 'trash' LIMIT 2;
+-----------+
| object_id |
+-----------+
| 2918088 |
| 2921240 |
+-----------+
2 rows in set (1.557 sec)
EXPLAIN SELECT `object_id` FROM `wp_yoast_indexable` WHERE `primary_focus_keyword` = 'test' AND `object_type` = 'post' AND `object_id` != '2952125' AND `post_status` != 'trash' LIMIT 2;
+------+-------------+--------------------+-------+---------------------------------------------+--------------------+---------+------+--------+------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+--------------------+-------+---------------------------------------------+--------------------+---------+------+--------+------------------------------------+
| 1 | SIMPLE | wp_yoast_indexable | range | object_type_and_sub_type,object_id_and_type | object_id_and_type | 9 | NULL | 779951 | Using index condition; Using where |
+------+-------------+--------------------+-------+---------------------------------------------+--------------------+---------+------+--------+------------------------------------+
1 row in set (0.001 sec)
ALTER TABLE `wp_yoast_indexable` ADD INDEX `primary_focus_keyword_and_object_type` (`primary_focus_keyword`, `object_type`, `post_status`, `object_id`);
Query OK, 0 rows affected (2.904 sec)
Records: 0 Duplicates: 0 Warnings: 0
SELECT SQL_NO_CACHE `object_id` FROM `wp_yoast_indexable` WHERE `primary_focus_keyword` = 'test' AND `object_type` = 'post' AND `object_id` != '2952125' AND `post_status` != 'trash' LIMIT 2;
+-----------+
| object_id |
+-----------+
| 2918088 |
| 2921240 |
+-----------+
2 rows in set (0.001 sec)
EXPLAIN SELECT `object_id` FROM `wp_yoast_indexable` WHERE `primary_focus_keyword` = 'test' AND `object_type` = 'post' AND `object_id` != '2952125' AND `post_status` != 'trash' LIMIT 2;
+------+-------------+--------------------+-------+-----------------------------------------------------------------------------------+---------------------------------------+---------+------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+--------------------+-------+-----------------------------------------------------------------------------------+---------------------------------------+---------+------+------+--------------------------+
| 1 | SIMPLE | wp_yoast_indexable | range | object_type_and_sub_type,object_id_and_type,primary_focus_keyword_and_object_type | primary_focus_keyword_and_object_type | 980 | NULL | 3 | Using where; Using index |
+------+-------------+--------------------+-------+-----------------------------------------------------------------------------------+---------------------------------------+---------+------+------+--------------------------+
1 row in set (0.001 sec)
Step-by-step reproduction instructions
Open any post for editing: /wp-admin/post.php?post=xyz&action=edit
Expected results
This should take 1 second.
Actual results
It takes more than 10 seconds.
Screenshots, screen recording, code snippet
No response
Which editor is affected (or editors)
- Block Editor
- Gutenberg Editor
- Elementor Editor
- Classic Editor
- Other (please specify in additional info)
Which browser is affected (or browsers)
- Chrome
- Firefox
- Safari
- Other (please specify in additional info)
Device you are using
No response
Operating system
No response
PHP version
No response
WordPress version
6.9
WordPress Theme
No response
Yoast SEO version
26.8
Gutenberg plugin version (if relevant)
No response
Elementor plugin version (if relevant)
No response
Classic Editor plugin version (if relevant)
No response
Relevant plugins in case of a bug
No response