MYSQL SET数据类型的替代方案

Question

i have a column where i want to store user's subscription (i.e what they have subscribed to on my subscription platform) i tried using SET datatype, but it has a Limit of 64 values, what users can subscribe to could rise beyond 64 and there will be a problem if a user subscribe above 64 items.

i also tried having another table that maps the user_id to the item_id upon subscription, but it could be a drawback when the database begins to grow.

Anyone with a better idea?

Answer 1

The normative approach for repeating attributes is a second table.

It's not at all clear why you think the separate table will be a drawback "when the database begins to grow".

---

FOLLOWUP

Q: If i have a second table that has up to 10,000 values of user's subscription and i am looking for a user's only one subscription it will take longer time to query. i am thinking of storing the user's subscription as an object in the users tables like a string object which i ll just query

A: No, a query of the second table won't take longer, if the second table has suitable indexes.

The query will actually be faster.

-- sample table

CREATE TABLE user_subscription 
( user_id         INT(10) UNSIGNED NOT NULL COMMENT 'PK, FK to user.id'
, subscription_id INT(10) UNSIGNED NOT NULL COMMENT 'PK'
, PRIMARY KEY (user_id,subscription_id)
, UNIQUE KEY user_subscription_UX1 (subscription_id,user_id)
) ENGINE=INNODB ;

-- populate

INSERT INTO user_subscription
SELECT s.d*10000 + u.d*1000 + h.d*100 + t.d*10 + o.d + 1 AS user_id, 1
  FROM digit s
  JOIN digit u
  JOIN digit h
  JOIN digit t
  JOIN digit o
 ORDER BY s.d, u.d, h.d, t.d, o.d ;
INSERT INTO user_subscription
SELECT s.user_id, s.subscription_id + 1
  FROM user_subscription s
 WHERE s.subscription_id = 1 ;      
INSERT INTO user_subscription
SELECT s.user_id, s.subscription_id + 2
  FROM user_subscription s
 WHERE s.subscription_id <= 2 ;      
INSERT INTO user_subscription
SELECT s.user_id, s.subscription_id + 4
  FROM user_subscription s
 WHERE s.subscription_id <= 4 ;      

-- total rows

SELECT COUNT(1) FROM user_subscription
COUNT(1)  
----------
    800000

-- sample query 1

EXPLAIN
SELECT t.*
  FROM user_subscription t
 WHERE t.user_id = 878

id select_type table  type   possible_keys key     key_len ref      rows Extra        
-- ----------- ------ ------ ------------- ------- ------- -----  ------ -----------
 1 SIMPLE      t      ref    PRIMARY       PRIMARY 4       const       8 Using index  

-- example query 2

EXPLAIN
SELECT t.*
  FROM user_subscription t
 WHERE t.subscription_id = 4

id select_type table  type possible_keys         key                   key_len ref      rows Extra
-- ----------- ------ ---- --------------------- --------------------- ------- -----  ------  -------------
1 SIMPLE       t      ref  user_subscription_UX1 user_subscription_UX1 4       const  184412  Using index

---

Compare that to storing the "subscriptions" of a user as a list in a VARCHAR column (the same representation in and out of the database as used for a SET datatype. And writing a query to identify users that have a particular subscription, e.g.

EXPLAIN
SELECT u.user_id
  FROM users u
 WHERE FIND_IN_SET(4,u.subscriptions)

You'll find that MySQL will need to inspect every row in the users table to see if the criteria is met.