Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

Problem

Some time ago I posted a question about the scalability of wp_usermeta architecture: my concern, as my client's database of users is growing fast, is now the memory usage of querying the wp_usermeta table.

In my situation, I am managing a CRM with thousands of users, each of which with around 15 meta fields. As I am getting this informations out of the database, the memory usage is exponentially growing, as much that now the 96M I set as a limit is not enough.

Data I collected (testing, profiling)

I have tried profiling and refactoring my code, by narrowing down exactly what I have to do, and I indeed optimized my queries as much as I could, depending on the situations:

  1. In most situations, I have to get the metadata associated to one user only. In this case, both running get_user_meta and get_userdata work fine.
  2. In some situations, I have to get the some metadata (~5 fields) associated to some users: using get_userdata exhausts my memory after ~2000 users; using get_user_meta with no $key (hence getting all the user associated metadata) exhausts my memory after ~3500 users; getting only the $keys I need actually exhausts the memory after ~3000 users.
  3. In a few situations, I have to get some metadata (~5 fields) associated to all users: this is where, obviously, I feel the problem the most.

I have tried several options: using the Wordpress APIs is apparently highly inefficient for this kind of situation, as I pointed out above. Examining the saved queries on $wpdb->queries also gives me a huge amount of unnecessary queries (I guess this is why getting all metas above is more efficient than getting a few metas).

It must be noted that, I also have to run a preliminary get_users to get the IDs to run the above mentioned functions on.

Since all those APIs also cache the results, I thought it might be a good idea to run my own DB queries, and indeed I save up loads of memory, using just around a total of 40M for around ~6000 users (my total now) with this query:

$wpdb->get_results( 
    "SELECT user_id, meta_value
     FROM $wpdb->usermeta
     WHERE meta_key='a' OR meta_key='b' OR meta_key='c' OR meta_key='d'"
);

Questions

  1. Am I doing something wrong in the approach?
  2. I have never worked with this amount of data, what is the memory usage I should expect for this kind of situation? (In order to understand my hosting needs)
  3. Would I be better off making a new database table? (The only thing that came to my mind, but I didn't try yet).
  4. If the custom DB query is a viable approach, how should I organize the data collected by that query in some WP_User-like object structure?
share|improve this question
    
Are you sure that your costume query will return 6k users? what are you going to do with it? –  Mark Kaplun Feb 28 '13 at 14:22
    
It doesn't return 6k users, it returns around 24k rows of metadata associated with them. I need this data to display filterable lists of users and relationships between users. –  Sunyatasattva Feb 28 '13 at 14:48
    
I fail to understand. No GUI can display anything related to 6k users. In other words, why don't you do the filtering in the query itself? –  Mark Kaplun Feb 28 '13 at 15:01
    
It does with the use of a paginated, filterable and searchable dynamic table. The most common use case for this application is the following: the agency has a set of manager users. Each manager user has possibly thousand of customer users assigned. He has to be able to glance at them and at a bunch of important data and also filter and search through them. –  Sunyatasattva Feb 28 '13 at 15:51
    
If it is going to be displayed paginated then why don't you ajax paginated results? –  Mark Kaplun Feb 28 '13 at 16:01

1 Answer 1

Just to put here the suggestion that came up after discussing the requirement on chat.

Due to the complexity of user filtering/search required, the wordpress user data tables is not a very good place to store the user's attributes. Instead it will make more sense to have a CPT and related taxonomies to store the user attributes required for the GUI, while maintaining basic information in the wordpress user table for login/authentication purposes.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.