I’ve built an enterprise WordPress site and we are currently designing an automated import of company user data directly into the WordPress database, so it’s not manual. This user data has more data points than the default WP user fields (user_login, first name, last name, email, url, etc…) can store. So I was wondering what the recommended architecture is for storing additional user fields?
My first thought was to add additional columns to the custom OpenID plugin I forked. It’s main purpose is to use the OpenID framework to leverage our corporate OpenID identifier to authenticate associates. This plugin creates a simple wp_openid_identities table in the database on activation:
uurl_id | user_id | url | hash | phone | state | country
------------------------------------------------------------------
1 | 1 | http://idp.example.com/user/9999 | 1^f3d5g7e9W!2 | 555-555-5555 | WA | USA
Or use the user_meta table and custom rows:
umeta_id | user_id | meta_key | meta_value
1 | 1 | first_name | Bill
2 | 1 | last_name | Gates
3 | 1 | nickname | bg000001
4 | 1 | rich_editing | true
5 | 1 | comment_shortcuts | false
6 | 1 | admin_color | fresh
7 | 1 | use_ssl | 0
8 | 1 | show_admin_bar_front | true
9 | 1 | wp_capabilities | a:1:{s:13:"administrator";b:1;}
10 | 1 | phone | 555-555-5555
11 | 1 | state | WA
12 | 1 | country | USA
etc…
I would guess that the user_meta table is the recommended place but the user base is currently 25,000+ users and growing with 12 additional custom user data points. That’s 19 default usermeta keys + 12 more (31). 25,000 * 31 = 7,750,000 rows!
Reporting: Join wp_users, wp_usermeta, and wp_custom_table (user submitted form data) where user_id is constant. This needs to be exported or echoed in a table format One row with all user info and form data.
1 Answer
I know this question is very old, but I wanted to share my experience with working with large databases and storing custom user data.
Basically wp_usermeta is the default and easiest option to store user meta and it works really well for smaller databases, where you might want to store a few extra fields for your users.
But if you have a large number of users and you need to store many fields and especially if you need to generate reports, you might want to avoid wp_usermeta and create a custom database table for your data where each field is stored in a separate column.
wp_usermeta becomes really slow to read with large number of records, especially if you need to generate reports for a high number of users in one query containing a high number of fields – in that case you would have to write an additional join for each field. The other disadvantage of using wp_usermeta is that all values are stored as strings, so you would need to cast them to the corresponding data types when reading/searching/sorting the data.
Using a custom table where each field is stored in a separate column has the following advantages:
- Increased performance
- You can define each column type and constraints, in this way it will be a lot less likely to store bad data. Also, you won’t need to cast the values when reading
- You can add custom indexes to further increase performance of frequent queries
It’s also worth mentioning that using a custom table is more complex to implement – you need someone experienced with databases to design the table and also you’ll have to write all your read/write SQL queries manually (though $wpdb provides easy to use methods for that).
In the end there’s no one size fits all solution – the best way to help you decide which approach is better for your project is to create some simple prototypes of the two approaches and benchmark the queries that you are planning to use.
