Questions about the performance of related user information in the comment table in the database?

scenario:
users leave comments on certain works, etc. A single table in the database stores these messages, which contains information such as the user"s profile picture and nickname.

Scheme:

user id, is used in the comment table to associate with the user table
each comment record directly stores the user"s id, profile picture and nickname together for easy reading.

question?
solution 1 was originally planned, but if there are more comments, you need to traverse each comment, take out the user id, query the information in the user table, and return the information to the front end. Won"t this be very slow? such an interface will query the database many times.

Plan 2: it is unreliable when you think about it, and there will be a lot of problems, (1) waste of space, (2) it will be troublesome for users to update information

< hr >

how do you deal with it? even if you tell me that Plan 1 is not slow, it is OK to dispel this doubt in my mind.

Please thank you

Backend-development-of backend database mongodb mysql

Mar.28,2021

scenario 1, there are a lot of comments, but you don't need to show all the comments every time, right? Only a part of it (such as 10 items) is shown to the user at a time, and it is not slow to select the constraints associated with the user table.
scenario 2, it doesn't matter that this space is wasted. Users' updates, nicknames and id should be limited to those that users can't modify, right? If you take a step back, you can modify the nickname. Personally, you don't need to update it to the comment table in real time. You can update it or schedule it when you pull the comment. As for the avatar, it should be a picture link, right? Do you want to deposit it directly in the table?

if there is not a lot of data, do whatever you want. The structure of this comment table is not complicated. Probably id,nickname,avatar, comment.., you can refer to which way others use more. Under normal circumstances, there is no performance problem, paging and indexing, that is, dry. If there is a lot of data and high real-time requirements, then use the second, even table query is certainly not as fast as single table query. Personal opinion

scenario 1, if you first take the comments of TOP N and then associate the information of the user table, a statement of sql federated query can query the results, and there should be no problem in terms of performance.

solution 2, which is obviously an anti-pattern design, needs to be confirmed with the product manager if the amount of data reviewed is not very large and the waste of space is not too big. Whether the key can accept the inconsistency of user data needs to be confirmed with the product manager. If you think that commenting on performance is the key to the whole system after communication, I think it is feasible to sacrifice some data consistency.

Previous: There's another algorithm problem, but I can't solve it.

Next: Submit, can get different values in the same list.