用于跟踪用户是否已查看项目的mysql数据库设计

时间:2021-08-19 04:17:26

I want to show a user only the content he has not viewed yet. I considered storing a string containing the ids of the items separated by ',' that a user has viewed but i thought i won't know the possible length of the string. The alternative i could find was to store it like a log. A table like

我想向用户显示他尚未查看的内容。我考虑过存储一个字符串,其中包含用户已查看的','分隔的项目的ID,但我想我不知道字符串的可能长度。我能找到的替代方案是将其存储为日志。像一张桌子

user_id | item_id
1       | 1
2       | 2
1       | 2

Which approach will be better for around ten thousand users and thousands of items.

对于大约一万个用户和数千个项目,哪种方法更好。

1 个解决方案

#1


1  

A table of pairs like that would be only 10M rows. That is "medium sized" as tables go.

像这样的对的表只有10M行。这是表中的“中等大小”。

Have

PRIMARY KEY(user_id, item_id),
INDEX(item_id, user_id)

And, if you are not going past 10K and 1K, consider using SMALLINT UNSIGNED (up to 64K in 2 bytes). Or, to be more conservative, MEDIUMINT UNSIGNED (up to 16M in 3 bytes).

并且,如果您没有超过10K和1K,请考虑使用SMALLINT UNSIGNED(2字节最多64K)。或者,更保守一点,MEDIUMINT UNSIGNED(3字节最多16M)。

#1


1  

A table of pairs like that would be only 10M rows. That is "medium sized" as tables go.

像这样的对的表只有10M行。这是表中的“中等大小”。

Have

PRIMARY KEY(user_id, item_id),
INDEX(item_id, user_id)

And, if you are not going past 10K and 1K, consider using SMALLINT UNSIGNED (up to 64K in 2 bytes). Or, to be more conservative, MEDIUMINT UNSIGNED (up to 16M in 3 bytes).

并且,如果您没有超过10K和1K,请考虑使用SMALLINT UNSIGNED(2字节最多64K)。或者,更保守一点,MEDIUMINT UNSIGNED(3字节最多16M)。