[wp-trac] [WordPress Trac] #63893: wp_usermeta – Unique Key (user_id+meta_key) - efficiency goal

[WordPress Trac]

#63893: wp_usermeta – Unique Key (user_id+meta_key) - efficiency goal
-------------------------------------------+-----------------------------
 Reporter:  curtisfraser                   |      Owner:  (none)
     Type:  enhancement                    |     Status:  new
 Priority:  normal                         |  Milestone:  Awaiting Review
Component:  Users                          |    Version:  6.8.2
 Severity:  normal                         |   Keywords:
  Focuses:  performance, coding-standards  |
-------------------------------------------+-----------------------------
 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

-- 
Ticket URL: <https://core.trac.wordpress.org/ticket/63893>
WordPress Trac <https://core.trac.wordpress.org/>
WordPress publishing platform