What's the most efficient database method to add and query usermeta?

Question

What's the most efficient way - concerning the database - to add and query extra usermeta fields to a profile?

I need to add four additional fields of numeric and text data to a user profile and then query that to compile the data for display in a template. And there are potentially thousands of users.

There are lots of examples to store the data in usermeta. But should this amount of data be stored in the usermeta table?

Or should I store it all in a new database table? And if so, how do I initialize a new table and then write to it?

Answer 1

There are lots of examples to store the data in usermeta. But should this amount of data be stored in the usermeta table?

I don't see any reason why you should not use the default user meta data table, after-all that is what it is for, regardless of the amount of users/fields.

The user meta function , such as get user meta uses get_metadata with the $meta_type object set to user. http://codex.wordpress.org/Function_Reference/get_metadata

If you want to mess around with new tables you can use the $wpdb class and create a new one, this post covers how, http://codex.wordpress.org/Creating_Tables_with_Plugins .

There is also a plugin called Pods CMS that allows for the creation of custom database tables for content types, but unless you have a specific reason for doing so, just use the built in user meta tables.

Answer 2

I have a Website (well a client of mine has it) which has just over 5000 users and for each user we store 9 different meta fields (rows) in the usermeta table and i really can't see any problems with that, i will add that 6 of these meta fields are actually arrays of data that hold from 4 to 13 different values (Integer, String and Boolean) so in most cases we only make one get_user_meta() call for all needed values of that page. and the other 3 fields are fields that we need to query by so they can't be in an array.

So overall each user has 80-96 custom fields which are all stored in 9 rows.

Now after that being said using a custom table will be faster (simple fact) but is it worth the trouble?