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
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:
- In most situations, I have to get the metadata associated to one user only. In this case, both running
- In some situations, I have to get the some metadata (~5 fields) associated to some users: using
get_userdataexhausts my memory after ~2000 users; using
$key(hence getting all the user associated metadata) exhausts my memory after ~3500 users; getting only the
$keysI need actually exhausts the memory after ~3000 users.
- 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'" );
- Am I doing something wrong in the approach?
- 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)
- Would I be better off making a new database table? (The only thing that came to my mind, but I didn't try yet).
- 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?