Shouldn't wp_usermeta have unique (user_id,meta_key) ?

  • pressward

    (@pressward)


    11 years, 4 months ago

    I just altered my wp_usermeta like so:

    alter table wp_usermeta add unique index unique_id_key (user_id,meta_key);

    and mysql did not complain of duplicates. Which, of course, indicates, that there aren’t any.

    So, I realize the idea is that the php code should make sure there aren’t duplicate entries, but why not ensure it on the MySQL level?

    I’m not sure if the “unique index” in particular causes a database performance hit. I don’t think it does, for reading, at least (maybe for writing — inserting/updating the indexed columns)

Viewing 5 replies - 1 through 5 (of 5 total)
  • halferdev

    (@halferdev)

    11 years, 4 months ago

    I don’t think this would be appropriate in all cases. If you look at the param signature for add_user_meta:

    add_user_meta( $user_id, $meta_key, $meta_value, $unique );

    The last parameter specifies whether the meta item must be unique. For example adding ‘nickname’ would be non-unique, since a person may have several nicknames. I should think the index you’re proposing would cause problems with some plugins, but you may get a performance improvement if you’re sure it will work for your site.

    Thread Starter pressward

    (@pressward)

    11 years, 4 months ago

    good point. Didn’t know about multiple-value situations. I’ll drop my unique index.

    Speaking of database modifications, I added a timestamp column to wp_options and other tables, in order to track changes and study the internals (I’m new to WP). Hope THAT doesn’t blow anything up.

    halferdev

    (@halferdev)

    11 years, 4 months ago

    Heh. In general I’d recommend being wary about changing the db format on live systems, since it is probably quite optimised as it is (for the general case, admittedly). But if it is for study, then it’s fine 🙂

    Thread Starter pressward

    (@pressward)

    11 years, 4 months ago

    Hah! Was about to close this thread as “solved” and then it occurred to me: Well, at least THIS much can be argued (right?):

    a unique index across ALL THREE columns (user_id, meta_key, meta_value)…

    (a) … would be/should be safe and proper

    (b) … might POSSIBLY be useful (at some point)… though I can’t readily say how ;-))

    halferdev

    (@halferdev)

    11 years, 4 months ago

    IMO, it’s not worth doing so. Certainly one would expect it not to fail, but then what happens if a plugin adds two keys having the same values? It might seem redundant to us when discussing it in the abstract, but it is possible there’s a use case for it.

Viewing 5 replies - 1 through 5 (of 5 total)
  • The topic ‘Shouldn't wp_usermeta have unique (user_id,meta_key) ?’ is closed to new replies.