How to Fix Yoast SEO for WordPress DeleteDuplicateIndexables MySQL error

There have been a number of reports about broken Yoast SEO database operations, related to the DeleteDuplicateIndexables function. The PHP function is located in the file wordpress-seo\src\config\migrations\20200507054848_DeleteDuplicateIndexables.php, and in this post I'll provide you with a possible fix for this statement. Read on.

Failing DeleteDuplicateIndexables statement

The failing DeleteDuplicateIndexables statement was introduced in Yoast SEO for WordPress GitHub issue #15065 and released in Yoast SEO version 14.1. The statement tries to delete all records from the table "wp_yoast_indexable" for which other records already exist with the same object_id and object_type.

Read Omar Reiss' blog post The exciting new technology that is indexables if you want to learn more about what Yoast calls "indexables".

From the 20200507054848_DeleteDuplicateIndexables.php file:

// from wordpress-seo/src/config/migrations/20200507054848_DeleteDuplicateIndexables.php
        public function up() {
                $table_name = $this->get_table_name();
                /*
                 * Deletes duplicate indexables that have the same object_id and object_type.
                 * The rows with a higher ID are deleted as those should be unused and could be outdated.
                 */
                $this->query( 'DELETE wyi FROM ' . $table_name . ' wyi INNER JOIN ' . $table_name . ' wyi2 WHERE wyi2.object_id = wyi.object_id AND wyi2.object_type = wyi.object_type AND wyi2.id < wyi.id;' );
        }

In SQL:

DELETE wyi
FROM wp_yoast_indexable wyi
INNER JOIN wp_yoast_indexable wyi2
WHERE wyi2.object_id = wyi.object_id
AND wyi2.object_type = wyi.object_type
AND wyi2.id < wyi.id;

The MySQL EXPLAIN output:

MariaDB [examplecom]> explain DELETE wyi FROM wp_yoast_indexable wyi INNER JOIN wp_yoast_indexable wyi2 WHERE wyi2.object_id = wyi.object_id AND wyi2.object_type = wyi.object_type AND wyi2.id < wyi.id
    -> ;
+------+-------------+-------+------+-----------------------------------------------------+--------------------+---------+-----------------------------------------------------------------+--------+--------------------------+
| id   | select_type | table | type | possible_keys                                       | key                | key_len | ref                                                             | rows   | Extra                    |
+------+-------------+-------+------+-----------------------------------------------------+--------------------+---------+-----------------------------------------------------------------+--------+--------------------------+
|    1 | SIMPLE      | wyi   | ALL  | PRIMARY,object_type_and_sub_type,object_id_and_type | NULL               | NULL    | NULL                                                            | 288649 | Using where              |
|    1 | SIMPLE      | wyi2  | ref  | PRIMARY,object_type_and_sub_type,object_id_and_type | object_id_and_type | 135     | examplecom.wyi.object_id,examplecom.wyi.object_type |   1658 | Using where; Using index |
+------+-------------+-------+------+-----------------------------------------------------+--------------------+---------+-----------------------------------------------------------------+--------+--------------------------+
2 rows in set (0.001 sec)

In cases where you have a lot of records in your wp_yoast_indexable table, this statement can easily cause a time-out. I've seen cases where it was killed by MySQL after more than one hour, for 290,000+ records and thus an extreme amount of duplicate records.

WordPress may log about this in its debug.log:

DeleteDuplicateIndexables – Error executing 'query' with:
DELETE wyi FROM wp_yoast_indexable wyi
INNER JOIN wp_yoast_indexable wyi2
WHERE wyi2.object_id = wyi.object_id
AND wyi2.object_type = wyi.object_type
AND wyi2.id < wyi.id;
Reason: Lock wait timeout exceeded; try restarting transaction"

Because the statement doesn't finish successfully, it's restarted the next time a visitor hits the website. And that's not something you want, because it's almost like an endless loop.

This is only an issue for very large databases, with (tens or hundreds of) thousands of duplicate records.

Let's improve and speed up this MySQL DELETE statement.

Optimizing the DeleteDuplicateIndexables SQL statement

By simply adding a where exists condition to the statement, we managed to get the statement to finish in 6 seconds. That's right, the statement that wouldn't finish in over an hour was now ready in merely 6 seconds!

delete from wp_yoast_indexable
where exists (
    select 1 from wp_yoast_indexable wyi2
    where wyi2.object_id = wp_yoast_indexable.object_id
    and wyi2.object_type = wp_yoast_indexable.object_type
    and wyi2.id < wp_yoast_indexable.id
);

Now we don't need that INNER JOIN either. This deleted 290,000+ duplicate records in just 6 seconds!

You may also notice the fully written out table name wp_yoast_indexable, and not its alias wyi. That is because of a MySQL limitation: you can't use a table alias for the target table in this form of DELETE statement. Therefore wp_yoast_indexable has to be fully written out in the exists clause.

wp_ is the table prefix as defined in your wp-config.php. Yours may be different, this is an example.

Always make a backup before you delete records by hand. If something goes wrong, you can restore a single MySQL table from a full mysqldump backup file.

I figured pitching in on one of the forum threads about this issue would get it resolved. I've also submitted this as GitHub issue #15328 in the Yoast SEO for WordPress repository.

More Yoast SEO and MySQL: query all WordPress posts in MySQL not having a Yoast SEO meta description, and MySQL database optimization with indices.