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

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() . "%"
  		);