wp_usermeta – Unique Key (user_id+meta_key) - efficiency goal

[curtisfraser]

Just curious as to why there is not a unique key for the wp_usermeta table using User_id and Meta_key?

At least within any website I have supported, there is no reason to have duplicate meta_key records for a User. Is there a reason it is not a unique key and indexed on this?

As I mentioned, for all of my clients, this SQL returns no records, as I expected.

select * from
(select distinct user_id, meta_key, count(*) as cnt from wp_usermeta group by user_id, meta_key) a
where cnt > 1
order by cnt desc;

This SQL to either insert or update for a meta key when it is indexed and has a unique key is much more elegant:

INSERT INTO wp_usermeta (user_id, meta_key, meta_value)
VALUES (@UserID, 'your_meta_key', 'your_value')
ON DUPLICATE KEY UPDATE meta_value = VALUES(meta_value);

Than:

UPDATE wp_usermeta
SET meta_value = ‘your_value’
WHERE user_id = @UserID AND meta_key = ‘your_meta_key’;

INSERT INTO wp_usermeta (user_id, meta_key, meta_value)
SELECT @UserID, ‘your_meta_key’, ‘your_value’
WHERE NOT EXISTS (
SELECT 1 FROM wp_usermeta
WHERE user_id = @UserID AND meta_key = ‘your_meta_key’
);

SQL to create said unique key:
ALTER TABLE wp_usermeta
ADD UNIQUE KEY usermeta_unique (user_id, meta_key);

The length of the column meta_key(255) makes creating the key length more than 1,000 bytes when using UTF-8. Which errors out.

This required changing the length of meta_key to 150.

In our 10,000's of metadata records, not one meta_key was more than 57 characters.

So three requests:

• reduce size of meta_key to 150 characters
• create unique key using user_id+meta_key
• create index on unique key

[siliconforks]

Replying to curtisfraser:

Just curious as to why there is not a unique key for the wp_usermeta table using User_id and Meta_key?

At least within any website I have supported, there is no reason to have duplicate meta_key records for a User. Is there a reason it is not a unique key and indexed on this?

Having multiple rows with the same user_id and meta_key is something which is supported by the metadata APIs:

​https://developer.wordpress.org/reference/functions/add_user_meta/
​https://developer.wordpress.org/reference/functions/get_user_meta/

In my experience, this feature is not something which is used very often, but there are a few plugins that do make use of this. (An example: ​Photo Reviews for WooCommerce.)

So three requests:

• reduce size of meta_key to 150 characters
• create unique key using user_id+meta_key
• create index on unique key

That might work fine on your site as long as you're not using any plugins that use this feature, but this is not something that is going to work everywhere.

[peterwilsoncc]

I agree with @siliconforks that this isn't something that can change in WordPress for the reason mentioned: the meta data APIs are designed to allow for multiple items with the same key to be stored against a single object (in this case a user).

While using a shorter field for the meta_key may be possible in some or even many cases, making the change would be a backward compatibility break as there's no way to be sure that of the many, many WordPress sites they all contain meta keys under 150 characters.