Help Weibo follow user table design-php Tutorial

Ask Weibo to follow the design of user tables. This post was last edited by anydy2008 at 10:43:13, January 11 ,.

The query requirements are as follows:
1. query which users are concerned.
2. query which users are paying attention.
3. query the list of followers shared by a user.
4. check whether there are mutual concerns.

The preceding functions can be implemented in the form of user_id follow_user_id add_time in a table.
However, the number of partitions is relatively large, and table sharding needs to be performed by user id. I don't know how to design the table structure.

Please give me more suggestions. Thank you.

Reply to discussion (solution)

Partition by user ID

User ID. each table uses a user ID.

Partition by user ID

I want to use a user ID for table sharding. However, after table sharding, the above functions cannot be implemented. For example, if I want to check which users follow me and divide the table, I am not sure which table to check. You can only query all the table shards once.

Save a follow table separately
Or is it feasible to save the users that follow you to the user table?

Because you are not sure which table to query after table sharding, you cannot partition the table instead.
The partitioned table is still an entire table (although it is divided into multiple files) for you. what you actually query is that the file is determined by mysql instead of you.

If you still want to split the table by yourself, you need to exchange the content of the user_id and follow_user_id columns to form a new table, and then split the two tables.

Partition by user ID

I want to use a user ID for table sharding. However, after table sharding, the above functions cannot be implemented. For example, if I want to check which users follow me and divide the table, I am not sure which table to check. You can only query all the table shards once.
Do not split tables. simply write SQL statements.

1. query which users are concerned about: select 'follow _ user_id 'from 'table' where 'user _ id' = 'Your ID'
2. query which users are interested in the query: select 'user _ id' from 'table' 'where' follow _ user_id '= 'Your ID'
3. query the list of followers shared by a user. Select * from 'table' where 'user _ id' in ('your ID', 'user ID ')
4. check for mutual interest: select * from 'table' where 'user _ id' = 'Your ID' and 'follow _ user_id '= 'User ID' 'user _ id' in (select 'follow _ user_id 'from' table 'where' user _ id' = 'User ID ')

Of course, it is estimated that there are more excellent writing methods, especially 4th

Select * from 'table 'where' user _ id' = 'Your ID' and 'follow _ user_id '= 'User ID' and 'user _ id' in (Select' follow _ user_id 'from' table 'Where 'user _ id' = 'User ID ')

If there is less than one and in 4th, write it again.

Because you are not sure which table to query after table sharding, you cannot partition the table instead.
The partitioned table is still an entire table (although it is divided into multiple files) for you. what you actually query is that the file is determined by mysql instead of you.

If you still want to split the table by yourself, you need to exchange the content of the user_id and follow_user_id columns to form a new table, and then split the two tables.

Thank you for your reference.

Because you are not sure which table to query after table sharding, you cannot partition the table instead.
The partitioned table is still an entire table (although it is divided into multiple files) for you. what you actually query is that the file is determined by mysql instead of you.

If you still want to split the table by yourself, you need to exchange the content of the user_id and follow_user_id columns to form a new table, and then split the two tables.

What is the main partition concept. I only know about computer partitioning.

Can you elaborate on this sentence?

"You need to exchange the content of the user_id and follow_user_id columns to form a new table, and then split the two tables"

Thank you!

For more information about table partitions, see http://www.baidu.com/s? Ie = UTF-8 & bs = mysql % E5 % 88% 86% E5 % 8C % BA % E8 % A1 % A8 & f = 8 & rsv_bp = 1 & wd = mysql % E5 % 88% e5 % 8C % BA & rsv_sug3 = 1 & rsv_sug = 1 & rsv_sug1 = 1 & rsv_sug4 = 26 & inputT = 796

Attention can be unidirectional, such

user_id follow_user_id1       21       31       4

After table sharding by user_id, you can check all the tables to obtain the user_id connected to follow_user_id.
Therefore, you need another group of tables grouped by follow_user_id.

follow_user_id user_id 2              13              14              1

For more information about table partitions, see http://www.baidu.com/s? Ie = UTF-8 & bs = mysql % E5 % 88% 86% E5 % 8C % BA % E8 % A1 % A8 & f = 8 & rsv_bp = 1 & wd = mysql % E5 % 88% e5 % 8C % BA & rsv_sug3 = 1 & rsv_sug = 1 & rsv_sug1 = 1 & rsv_sug4 = 26 & inputT = 796

Attention can be unidirectional, such

user_id follow_user_id1       21       31       4

After table sharding by user_id, you can check all the tables to obtain the user_id connected to follow_user_id.
Therefore, you need another group of tables grouped by follow_user_id.

follow_user_id user_id 2              13              14              1

Moderator
"Tables in the follow_user_id group" should also be sharded.
So this solution can be checked. who cares about me.
For example, if my user ID is 1, table Sharding is performed by the tail number of follow_user_id.


Follow_user_tbl_1

Follow_user_id user_id
1 1
11 1
21 1

Follow_user_tbl_2

Follow_user_id user_id
2 1
12 1
22 1

If user_id follow_user_id indicates that user_id is followed by follow_user_id
Follow_user_id user_id indicates that follow_user_id follows user_id

After you confirm the subject, the matter will be clear.

Save a follow table separately
Or is it feasible to save the users that follow you to the user table?

It is not feasible to save a follow table separately because the data volume is too large.

It is unreasonable to keep my users in the user table.

If user_id follow_user_id indicates that user_id is followed by follow_user_id
Follow_user_id user_id indicates that follow_user_id follows user_id

After you confirm the subject, the matter will be clear.

The moderator listened to you and began to understand your thoughts. Thank you. This is the only way to split tables.