Slow Query on WPML String Translation Page for Sites with a Large User Base

Status: Resolved
Reported for: WPML Multilingual CMS 4.6.7
Resolved in: WPML 4.9
Topic tags: Bug, Performance

Overview of the issue

If you are using WPML String Translation on a WordPress site with a significant number of users, you may experience a substantial delay when accessing the WPML > String Translation page. This issue stems from a specific query that becomes increasingly slow as the number of users grows.

Workaround

Please, make sure of having a full backup of your site before proceeding.

Open …/wp-content/plugins/sitepress-multilingual-cms/classes/user/class-wpml-translation-roles-records.php file.

Look for line 127.

Change:

		$preparedUserQuery = $this->wpdb->prepare(
			"SELECT u.id FROM {$this->wpdb->users} u INNER JOIN {$this->wpdb->usermeta} c ON c.user_id=u.ID AND CAST(c.meta_key AS BINARY)=%s AND c.meta_value {$compare} %s",
			"{$this->wpdb->prefix}capabilities",
			"%" . $this->get_capability() . "%"
		);

For:

		$preparedUserQuery = $this->wpdb->prepare(
			"SELECT u.id FROM {$this->wpdb->users} u INNER JOIN {$this->wpdb->usermeta} c ON c.user_id=u.ID AND c.meta_key=%s AND c.meta_value {$compare} %s",
			"{$this->wpdb->prefix}capabilities",
			"%" . $this->get_capability() . "%"
		);

All known issues →