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

Status

Resolved

Type

Bug

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