Performance considerations for the WPML Media setup on large sites

Overview of the issue

As of version 2.3.0 of the WPML Media Translation plugin, it is possible to translate and synchronize media between translations.

As part of the setup process (that also runs when you migrate from an earlier version), WPML Media scans the whole site for media URLs used in the content.

Flagging the posts that contain media helps down the road so, on certain events related to media translation (such as translating a certain media post), only posts that actually include media will be scanned in order to update the content with translated media.

However, for very large sites, the setup process can put some pressure on the database making the whole process slower than expected.

For the largest part, the strain is caused by queries needed to identify attachment IDs based on URLs. Normally, the attachment ID can be determined from the attributes of an HTML tag (e.g. id=”attachment_8″ – here, 8 is the ID of the attachment). When such an attribute is not used, WPML Media determines the ID from the actual URL. In this case, two type of queries are used:

• A query that matches against the ‘guid’ field in the wp_posts table
• Another query that matches against the meta_value fields of the wp_postmeta table.

None of these table fields, however, are indexed by default. Since it’s a text match (even if not a partial text search), for large databases it can be slow.

Workaround

A way to go around this problem is to (at least temporarily) add indexes for these fields.
You can do this using two SQL queries like the following ones:

ALTER TABLE `wp_posts` ADD INDEX `guid` (`guid`);
ALTER TABLE `wp_postmeta` ADD INDEX `meta_value` (`meta_value`(512));

These additions should not have any side effects on your database other than making the physical size of the database slightly larger on the disk. If you prefer to roll them back after running the WPML Media setup, you can do it using the following two MySQL queries:

ALTER TABLE `wp_posts` DROP INDEX `guid`;
ALTER TABLE `wp_postmeta` DROP INDEX `meta_value`;

Note: the wp_ prefix for the tables mentioned in the queries can be different as per your WordPress configuration. Usually and by default, it is wp_

Case study example: on a site with about 140,000 attachments and about 5,000 posts, the setup can take more than 30 minutes to complete. After adding the indexes, the setup runs in a couple of minutes. The tests were made on a regular development environment. These values can be even more favorable on an actual production server.