latest stable versions: v150827 (changelog)

Old Forums (READ-ONLY): The community now lives at WP Sharks™. If you have an s2Member® Pro question, please use our new Support System.

Extremely slow user_meta query

Home Forums Community Forum Extremely slow user_meta query

Tagged: 

This topic contains 4 replies, has 2 voices. Last updated by  Cristián Lávaque 3 years, 5 months ago.

Topic Author Topic
Posted: Wednesday Jul 17th, 2013 at 3:32 am #53553

MYSQL’s slow_query log shows multiple entries for the following query:

SELECT SQL_CALC_FOUND_ROWS wp_users.ID  FROM `wp_users` INNER JOIN `wp_usermeta` ON `wp_users`.`ID` = `wp_usermeta`.`user_id`  WHERE '1' = '1' AND ( (`wp_usermeta`.`meta_key` = 'wp_s2member_subscr_id' AND `wp_usermeta`.`meta_value` LIKE '%[USER_LOGIN]%') OR (`wp_usermeta`.`meta_key` = 'wp_s2member_custom' AND `wp_usermeta`.`meta_value` LIKE '%[USER_LOGIN]%') OR (`wp_usermeta`.`meta_key` = 'wp_s2member_custom_fields' AND `wp_usermeta`.`meta_value` LIKE '%[USER_LOGIN]%') OR (`wp_usermeta`.`meta_key` = 'wp_s2member_notes' AND `wp_usermeta`.`meta_value` LIKE '%[USER_LOGIN]%') OR `user_login` LIKE '%[USER_LOGIN]%' OR `user_nicename` LIKE '%[USER_LOGIN]%' OR `user_email` LIKE '%[USER_LOGIN]%' OR `user_url` LIKE '%[USER_LOGIN]%' OR `display_name` LIKE '%[USER_LOGIN]%') AND `wp_users`.`ID` IN(SELECT DISTINCT(`user_id`) FROM `wp_usermeta` WHERE `meta_key` = 'wp_capabilities') ORDER BY user_login ASC LIMIT 20

I already added a compound index on user_id & meta_key as suggested in another thread but this did not seem to help. Any ideas on how to optimize for better response times?

List Of Topic Replies

Viewing 4 replies - 1 through 4 (of 4 total)
Author Replies
Author Replies
Posted: Thursday Jul 18th, 2013 at 7:14 am #53606

After searching in the files, I’m guessing that query is from the [hilite mono]users_list_query[/hilite] function. I’ll ask Jason what he suggests and let you know as soon as I hear back from him.

Posted: Friday Jul 26th, 2013 at 5:57 am #54130

Here’s Jason’s reply:

The first thing I would ask is how many users they have, roughly. Are we talking over 100+ or what? Having some feedback like this and stats on the number of users that caused the lag against this query will help us improve.

I also recommend checking the database table itself and making sure it’s not fragmented with lots of overhead. Run the OPTIMIZE command in phpMyAdmin to cleanup any fragmented tables.

I would also take a look at the wp_usermeta table. Has another plugin somehow stuffed thousands (or even millions) of unnecessary records in that table by mistake? Maybe the plugin is no longer running, but it may have caused overhead by adding unnecessary records to that table somehow.

If all else fails, s2Member makes two additional filters available.

This would speed things up considerably.

[hilite pre_code]

Posted: Sunday Jul 28th, 2013 at 12:59 am #54259

Thanks for getting back to us. We have 120K users and wp_usermeta is about 1.9M rows. Not sure if this is a typical ratio or not but we’ll look into pruning any data that looks unnecessary.

re: s2 filter

Do we apply this in our functions file?

Posted: Sunday Jul 28th, 2013 at 1:16 am #54260

You can add it as a must-use plugin.

With that code, create the dir/file: /wp-content/mu-plugins/s2-hacks.php
See: Knowledge Base » Hacking s2Member® Via Hooks/Filters

Viewing 4 replies - 1 through 4 (of 4 total)

This topic is closed to new replies. Topics with no replies for 2 weeks are closed automatically.

Old Forums (READ-ONLY): The community now lives at WP Sharks™. If you have an s2Member® Pro question, please use our new Support System.

Contacting s2Member: Please use our Support Center for bug reports, pre-sale questions & technical assistance.