|
Background of the issue:
Hello,
I've had huge performance issues with my website and I finally uncovered the culprit. It turned out that for a db of 5k woocommerce products in 3 languages = 15k posts, I actually had nearly half a milion posts in the database with all the corresponding wp_postmeta (almost 3M records).
Upon further inspection, it turned out that nearly all of the posts are of type attachment, that they come from WPML duplicating records for translating media metadata and that while turning WPML on and off several times (including the support tickets here) last year, when I was setting it up initially, I duplicated media meta several times, which turned into this exponential cascade of db records.
Steps I've already taken:
0. Searched WPML support forum and found a lot of similar issues.
1. Reconfigured WPML to never translate media, but this doesn't work retroactively.
2. Run media cleaner, but this was taking hours and returned a lot of unspecified "error server responses". This wasn't much help.
3. Run different SELECT queries on the db to asses the situation. In the end, I've decided to do something drastic:
DELETE post, meta
FROM wp_posts post
INNER JOIN wp_postmeta meta ON post.ID = meta.post_id
WHERE post.post_name REGEXP '^.+-[3-9]+$' AND post.post_type = 'attachment'
You might ask, why 3 and not 1 in the regex. Here lies the current problem I need help with. This cleaned up 400 out of 440k of my wp_posts and most of wp_postmeta. My website performance is mostly fixed, but the cleanup is not complete yet.
Link to a page where the issue can be seen: hidden link
Symptoms:
Expected behavior: After setting WPML to never translate media, _thumbnail_id meta from translated product posts gets ignored and translated products always get their base language image file.
Actual behavior: The translated products now have a broken link to the media file, whose corresponding wp_post doesn't exist. Moreover, deleting the _thumbnail_id meta from the translated product still doesn't help - what happens instead is that the default woocommerce product placeholder image gets shown.
Questions:
My goal: wp_posts should contain only 3 * 5k records for the products in 3 languages + 1 * 5k for untranslated media attachment records + 1k of unrelated records (menus, etc). No media should be translated and all translated products should display the media file from the base translation.
Please advise on the best course of action.
Best Regards,
Jaroslaw Watroba
|
