How to Fix Yoast SEO for WordPress DeleteDuplicateIndexables MySQL error

In recent days, there are 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' blogpost 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;

MySQL EXPLAIN

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 extremely amount of double records.

WordPress' may log about this in it 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 isn't finished 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 duplicate records.

Let's improve and speed-up this MySQL delete statement.

Optimizing the DeleteDuplicateIndexables SQL statement

for now, this solution only works with MariaDB! For Oracle MySQL, the Yoast Test Helper plugin might help by dropping and/or recreating certain tables. I have not tried this thus I have no experience with it.

By simply adding a where exists condition to the statement, we managed to get the statement finish in 6 seconds. That 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+ double records in just 6 seconds!

You may also notice a fully written out table name wp_yoast_indexable. And not its alias wyi. That is because it's MySQL limitation you can’t use a table alias with a DELETE statement. Therefore wp_yoast_indexable has to be fully written out in the exists clause, instead of using an alias wyi.

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

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