Help Weibo focus on user table design

This post was last edited by anydy2008 on 2013-10-11 10:43:13

The query needs are as follows:
1. Query which users are concerned.
2. Which users are concerned about the query.
3. Query and a common list of concerns for a user.
4. Whether the query concerns each other.

The above functions can be achieved with a table in the form of user_id follow_user_id add_time columns.
But the attention volume is larger, need to according to user ID to divide the table. I don't know how to design a table structure well.

Ask your friends, please give me a lot of advice. Thank you

Reply to discussion (solution)

Partition with User ID

User ID, with a user ID between each table

Partition with User ID

Moderator I want to use the user ID to divide the table, but the table after the above function is not good to achieve. For example, I want to check which users follow me, after the table, I am not sure which table to check. It is only possible to find out all the sub-tables once.

Save a watchlist on your own
Or will it be possible to save your user's attention to the user's table?

It is not possible to partition a table, because it is not sure which table to check after the table is divided.
The partitioned table is still an entire table for you (although it is divided into multiple files), the actual query is that the file is determined by MySQL rather than you decide

If you still want to divide your own table, then you need to Exchange user_id, follow_user_id columns of content to form a new table, and then the two table sub-table

Partition with User ID

Moderator I want to use the user ID to divide the table, but the table after the above function is not good to achieve. For example, I want to check which users follow me, after the table, I am not sure which table to check. It is only possible to find out all the sub-tables once.
Do not divide the table ah, directly write the SQL statement can be

1. Query which users are concerned: Select ' follow_user_id ' from ' table ' where ' user_id ' = ' Your id '
2. Query by which users are concerned: Select ' user_id ' from ' table ' where ' follow_user_id ' = ' Your id '
3. Query and a common list of concerns for a user. SELECT * from ' table ' where ' user_id ' in (' Your id ', ' A user ID ')
4. Whether the query concerns each other: SELECT * from ' table ' where ' user_id ' = ' your id ' and ' follow_user_id ' = ' A user ID ' user_id ' in (select ' follow_user_id ' fr Om ' table ' where ' user_id ' = ' a user ID ')

Of course, there are better formulations, especially 4th.

SELECT * from ' table ' where ' user_id ' = ' your id ' and ' follow_user_id ' = ' A user ID ' and ' user_id ' in (select ' follow_user_id ' from ' table ') where ' user_id ' = ' a user ID ')

4th, one less and, re-write.

It is not possible to partition a table, because it is not sure which table to check after the table is divided.
The partitioned table is still an entire table for you (although it is divided into multiple files), the actual query is that the file is determined by MySQL rather than you decide

If you still want to divide your own table, then you need to Exchange user_id, follow_user_id columns of content to form a new table, and then the two table sub-table

Thanks for the answer, I'll take a look.

It is not possible to partition a table, because it is not sure which table to check after the table is divided.
The partitioned table is still an entire table for you (although it is divided into multiple files), the actual query is that the file is determined by MySQL rather than you decide

If you still want to divide your own table, then you need to Exchange user_id, follow_user_id columns of content to form a new table, and then the two table sub-table

What is the concept of moderator partitioning? I only know the computer division.

Can you tell me more about this sentence?

"Then you need to swap the contents of the user_id, follow_user_id columns to form a new table, and then divide the table by two"

Thank you!

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= for table partitioning mysql%e5%88%86%e5%8c%ba&rsv_sug3=1&rsv_sug=1&rsv_sug1=1&rsv_sug4=26&inputt=796

Attention can be one-way, like this.

user_id follow_user_id1       4

By user_id the table, you check all the tables to get the user_id connected to the follow_user_id.
So you need a different set of tables that are grouped in follow_user_id

follow_user_id user_id 2              1

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= for table partitioning mysql%e5%88%86%e5%8c%ba&rsv_sug3=1&rsv_sug=1&rsv_sug1=1&rsv_sug4=26&inputt=796

Attention can be one-way, like this.

user_id follow_user_id1       4

By user_id the table, you check all the tables to get the user_id connected to the follow_user_id.
So you need a different set of tables that are grouped in follow_user_id

follow_user_id user_id 2              1

Moderator
The "follow_user_id" table is also a separate table.
Then this program can support the search, who is concerned about me?
For example, my user ID is 1 by follow_user_id the mantissa of the table.


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 says USER_ID is follow_user_id concerned
So follow_user_id User_ID said follow_user_id was concerned about USER_ID

You've identified the subject, and it's clear.

Save a watchlist on your own
Or will it be possible to save your user's attention to the user's table?

Saving a watchlist alone is not feasible because the data is too large.

It's not reasonable to follow my user's Save to user table

If user_id follow_user_id says USER_ID is follow_user_id concerned
So follow_user_id User_ID said follow_user_id was concerned about USER_ID

You've identified the subject, and it's clear.

Moderator I listened to you, and began to understand your thoughts. Thank you, sir. It's probably the only way to divide the table.

