3

Alongside the users table, Wordpress has a usersmeta table with the following columns

  • meta_id
  • user_id
  • meta_key (e.g. first_name)
  • meta_value (e.g. Tom)

Each user has 20 rows in the usersmeta table, regardless of whether or not the rows have a filled-in meta_value. That said, would it not be more efficient to add the always-present meta rows to the users table?

I'm guessing that the information in the users table is more frequently queried (e.g. user_id, username, pass), so it is more efficient to keep those rows smaller. Is this true? And are there other reasons for this separation of tables?

Voriki
  • 1,617
  • 2
  • 19
  • 43
  • I guess they want to support an arbitrary number of meta keys? – Mike Christensen Jun 12 '12 at 22:52
  • @Mike But couldn't they just add/remove the same keys from the users table? E.g. When they create a new meta key, in the current structure they'd have to insert a new row in the usersmeta table for each user. If the meta keys were all in the users table, then they would just add a new column to the entire table. I might be missing something, but both support an arbitrary number of meta keys, no? – Voriki Jun 12 '12 at 22:56
  • 1
    Perhaps there might be 500 different possible `meta_key` values, but most users only have a handful. They wouldn't want 500 columns on the user table. Also, perhaps different plugins can store metadata on a user and you wouldn't want that modifying your DB schema. Really, I can't speculate. Perhaps someone who's more of a Wordpress expert will chime in. – Mike Christensen Jun 12 '12 at 23:01
  • 1
    @Mike The reason the 500 columns didn't seem like a problem is that as of now, each user has the same amount of rows in the meta table, regardless of whether there is a corresponding value. So with 500 meta keys they would still have 500 rows each in the meta table, or 500 columns in the users table (if they were combined). But I didn't even consider that second part. Plugins adding rows to a meta table makes much more sense than letting them change the database structure. I think that may very well be it. – Voriki Jun 12 '12 at 23:05

2 Answers2

6

Entity Attribute Value

It's known as the Entity Attribute Value (EAV) data model, and allows an arbitrary number of attributes to be assigned to a given entity. That means any number of meta-data entries per user.

Why use it

By default there are a few keys that wordpress sets (20 stated in the question) but there can be any number. If all users have one thousand meta data entries - there are simply one thousand entries in the usermeta table for each user - it doesn't have (in terms of the database structure) a limit to the number of meta data entries a user can have. It also permits one user to have one thousand meta data entires, whilst all others have 20 and still store the data efficiently - or any permutation thereof.

In addition to flexibility, using this kind of structure permits the main users table to remain small - which means more efficient queries.

Alternatives

The alternatives to using EAV include:

  • Modify the schema whenever the number of attributes changes
  • Store all attributes in a serialized string (on the user object)
  • Use a schemaless db

Permissions is the biggest problem with the first point, it is not a good idea to grant blanket access to alter the schema of your database tables, and is a (sane) roadblock for many if not most wordpress installs (hosted on wordpress.com or on a shared host where the db user has no alter permissions). Mysql also has a hard-limit of 4096 columns and 65,535 bytes per row. Attempting to store a large number of columns in a single table will eventually fail, along the way creating a table that is inefficient to query.

Storing all attribute in a serialized string would make it difficult and slow to query by a meta-data value.

Wordpress is quite tied to mysql, and therefore changing datastore isn't a realistic option.

Further WP info

If you aren't using any/many plugins it's possible you will have a constant number of rows in the usermeta table for each user, but typically each plugin you add may need to add meta-data for users; the number added may not be trivial and this data is stored in the usermeta table.

The docs for add_meta_user may add some clarity as to why the database is structured that way. If you put code like this somewhere:

add_user_meta($user_id, "favorite_color", "blue");

It will create a row in the usermeta table for the given user_id, without the need to add a column (favorite_color) to the main users table. That makes it easy-ish to find users by favorite color without the need to modify the schema of the users table.

Community
  • 1
  • 1
AD7six
  • 63,116
  • 12
  • 91
  • 123
  • 1
    Great answer AD7. I would just add, if you write something that adds favorite_color, it may automatically add rows for each defined users if the edit their page even if they dont touch the color field – Ryan B Jun 14 '12 at 15:36
3

This is really a question about database normalization. You can look for information on that topic in many places.

Basic answer Since there is a huge literature about this, and there are a lot of differences, I will just give some examples of why this might happen - it boild down to trade-offs; Speed versus storage requirements, or ease of use versus data duplication. Efficiency is multidimensional, and since wordpress does a lot of different things, it may have various reasons to keep them separate - space could be an issue, speed of queries may depend on this, it may be easier to look at just the meta table instead of the full table for some purposes, or vice versa.

Further reading This is a deep topic, you may want to learn more - there are hundreds of books and thousands of scholarly papers on these issues. For instance, look at this previous SO question about designing a database: Database design: one huge table or separate tables?, or this one: First-time database design: am I overengineering? or Database Normalization Basics on About.com.

Community
  • 1
  • 1
David Manheim
  • 2,553
  • 2
  • 27
  • 42