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.

I have developed a CRM plugin for a client integrated with Wordpress user management and I stored additional information for each user under the wp_usermeta table.

However, the client's customer database is growing exponentially, and we are now in the thousands, which gives us several tens of thousands rows in wp_usermeta: at this point I am starting to be worried about scalability of this architecture.

Does anybody have any experience on managing such an amount of users the Wordpress way? Should I add columns to the wp_users table instead of relying on the wp_usermeta one? How can I diagnose/profile Wordpress and my own code performance at this stage?

I have never worked on such an amount of data and at this increasing rate, and any pointer would be valuable.

share|improve this question

2 Answers 2

up vote 8 down vote accepted

The size of the table isn't really the issue, the queries you're running on that table might be.

For example, if you're selecting users based on data stored in the user-meta table, then that query will be highly unoptimized, because meta_value is not an indexed field. In which case you may need to add extra indexes or consider storing that particular data in a different manner, such as with a custom taxonomy.

Generally speaking, stuff that you store as meta should never be something that you will exclusively search based on. This applies across all the meta tables in WordPress. Meta is mainly designed to be pulled out by the meta_key, not by the meta_value. Taxonomies limit the possible values to a set and organize the information differently, so they do better when the "value" counts as what you're selecting on.

Note, selecting by both meta_key and meta_value is generally okay, because mySQL will optimize the query to be based on the meta_key first, reducing the amount of data to search to a (hopefully) manageable limit. If even this becomes a problem, you can "fix" it by adding a new index to the meta table with both meta_key and meta_value on the index, however because meta_value is LONGTEXT, you need to limit the length of that index to something reasonable, like 20-30 or something, depending on your data. Note that this index may be much, much larger than your actual data and will drastically increase the storage space needed. However, it will be much faster at those types of queries. Consult a qualified DBA if this ever becomes a real issue.

For reference, on WordPress.org we have approximately 11 million users registered. The amount of meta varies per user, with probably a minimum of 8 rows per, and maybe a max of around 250-ish. The users table is about 2.5 GB, the usermeta table around 4 GB. Seems to run okay, for the most part, but every once in a while we find some oddball query that we have to go optimize.

share|improve this answer
    
does wordpress.org index the meta table? and if so, can you give examples of how it's configured? –  dwenaus Feb 15 '13 at 7:48
1  
The usermeta table has no more indexing than default on WordPress.org. There is one meta table used in bbPress where we added an extra KEY, of the form (object_type,meta_key,meta_value(50)) –  Otto Feb 15 '13 at 14:26
    
Thanks Otto. Do you have any particular tip for profiling/diagnosing my Wordpress query performance? –  Sunyatasattva Feb 15 '13 at 19:35
2  
Not really a WordPress related question there, look more into MySQL profiling and such. You can use some simple tools like the Debug Bar plugin to see queries WordPress makes and their times, but these tools are rudimentary and a real MySQL profiling mechanism would be better. You need to identify the real bottlenecks before doing optimizations. –  Otto Feb 16 '13 at 0:18
    
Thanks for the pointers! –  Sunyatasattva Feb 16 '13 at 1:53

Unless you are running your own queries instead of using the API, the size of table doesn't matter that much as wordpress runs queries by the indexes of the table and MYSQL supposed to optimize this kind of queries. Each query also fetches all meta information in one query.

If you insist you can split the user meta table into several tables using an hash on the user ID as the table name, but then you will probably have to write a replacement to the wp_db class to access the right table based on the query. If you are interested in following this path then look for solutions for handling big network installs with many blogs.

But if you don't have any performance issues now then you can grow much further without doing any significant adjustment. When you start getting performance issues just move the DB to a faster server, it will be more cost effective than any manipulation you can do to the way WP access that info.

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.