[Resolved] Optimizing the database

Hello,

We thoroughly analyzed your DB, and couldn't confirm that the issue was indeed being caused by WPML.

1. First, we checked the size of the _postsmeta table, and it was indeed the largest one. Check image wpml_001;
2. We also checked, and in _posts, you have more than 1 million entries there, which is a lot higher than your actual number of media entries. Check image wpml_002;
3. There are 24,924 rows with attachment "new survey" created "2024-11-28 13:56:22" exactly... This indicates some issue, and it is not new according to the date;
4. By sorting the _postmeta table by metakey, we noticed that the entries created by wpml, are similar to the number of entries created by the attachments, which is expected behavior. Check image wpml_003;

This information is not conclusive, but there's a strong indication that there's something bloating the DB. Since WPML has to create entries in other languages for your website's content, it's following that pattern.

To move forward with that, since I could not import the DB files you sent (which are apparently corrupt), what I can recommend is for you to create a copy or staging version of your website. Then, remove the attachment meta and attachment post-entries that do not have an entry in the icl_translations table. I couldn't test the queries, since I don't have a copy, but you can try using the ones below:

To list all attachments without an entry:

SELECT p.ID
FROM `93lzr5_posts` p
LEFT JOIN `93lzr5_icl_translations` t ON p.ID = t.element_id
WHERE p.post_type = 'attachment'
AND t.element_id IS NULL;

Then you can run this query to remove the postmeta:

DELETE pm
FROM `93lzr5_postmeta` pm
WHERE pm.post_id IN (
    SELECT p.ID
    FROM `93lzr5_posts` p
    LEFT JOIN `93lzr5_icl_translations` t ON p.ID = t.element_id
    WHERE p.post_type = 'attachment'
    AND t.element_id IS NULL
);

And finally, to remove the posts

DELETE p
FROM `93lzr5_posts` p
LEFT JOIN `93lzr5_icl_translations` t ON p.ID = t.element_id
WHERE p.post_type = 'attachment'
AND t.element_id IS NULL;

As I said, this has not been tested and must not be done in the official website. Perhaps cleaning this suspect entries will also reduce the size of WPML entries, as they correspond to the existing ones, as expected.