MySQL Vertical split data table

During the development process, I met the question: there is an article table in the database that holds information about the article (such as: title, content, author, creation time, Number of FavoritesAnd so on), there is another table to save the user's and article's Favorites (the primary field is the user ID, the article ID), and the data is cached to Redis when the article is first viewed, thereby improving performance.
But now because the article table is updated every time you bookmark or cancel a favorite article Number of FavoritesThis field, and will empty the cache of the article (the reason is to choose to empty instead of updating the cache, in order to avoid dirty data), the next time you access the article will query the database and cache the data, so the performance will have a certain impact!

So I decided to split the collection of article tables to create a new Article collection table (the main fields are: Primary key ID, article ID, collection), and cache the article data when the collection is cached separately, so that each collection or cancel the collection of articles when the article table does not update, only update the article collection tables, update the article collection tables, Update the cache directly (there is no fear of generating dirty data because the collections are not particularly important data, even if there is a certain error will not cause bad effects).

Using the above scheme, resulting in two questions, we hope to get answers:

1. Because the primary key ID and the article ID two fields in the article collection are unique and non-repeating fields, can the primary key ID field be used and the article ID as the primary key directly?

2. Because if each new article data is not synchronized to add a corresponding article ID of the article collection data, each time you collect and cancel the collection of articles in the collection of articles, you will need to query the article collection number of the number of the corresponding article ID in the collection, if not a new article collection of data, If there is a direct update of the article's collection, so, for the sake of simplicity, I decided to add a new article every time the data is added a corresponding article ID of the article Collection data , in the future to update the collection of articles do not need to determine the existence of data, and can directly update the data, in this case, How to avoid new article data and new article collection data between the exception causes the new article data success and new article collection data failure? I know I can use transactions to avoid, can there be no easier way? Can MySQL add a new piece of data and automatically add a new piece of data to the other table? (Database slag ~)

Above, thanks!

Reply content:

In the development process, I met a question: in the database there is an article table to save information about the article (such as: title, content, author, creation time, collection , etc.), there is another table to save the user and the article's collection (the main fields are: User ID, article ID) Improves performance by caching data from the database to Redis when there is a first view of the article.
But now, because each time you bookmark or cancel a favorite article, the list of favorites is updated, and the cache of the article is emptied (the reason you chose to empty instead of updating the cache is to avoid generating dirty data), and the next time you access the article, the database is queried and the data is cached. Therefore, the performance will have a certain impact!

So I decided to split the collection of article tables to create a new Article collection table (the main fields are: Primary key ID, article ID, collection), and cache the article data when the collection is cached separately, so that each collection or cancel the collection of articles when the article table does not update, only update the article collection tables, update the article collection tables, Update the cache directly (there is no fear of generating dirty data because the collections are not particularly important data, even if there is a certain error will not cause bad effects).

Using the above scheme, resulting in two questions, we hope to get answers:

1. Because the primary key ID and the article ID two fields in the article collection are unique and non-repeating fields, can the primary key ID field be used and the article ID as the primary key directly?

2. Because if each new article data is not synchronized to add a corresponding article ID of the article collection data, each time you collect and cancel the collection of articles in the collection of articles, you will need to query the article collection number of the number of the corresponding article ID in the collection, if not a new article collection of data, If there is a direct update of the article's collection, so, for the sake of simplicity, I decided to add a new article every time the data is added a corresponding article ID of the article Collection data , in the future to update the collection of articles do not need to determine the existence of data, and can directly update the data, in this case, How to avoid new article data and new article collection data between the exception causes the new article data success and new article collection data failure? I know I can use transactions to avoid, can there be no easier way? Can MySQL add a new piece of data and automatically add a new piece of data to the other table? (Database slag ~)

Above, thanks!

1. Yes, but here you need to do a linkage is to add and delete articles at the time also to the article collection of tables to operate

2 using the MySQL UDF, see MySQL for details:: MySQL 5.1 Reference Manual:: 22.3 Adding New Functions to MySQL then through trigger in the article table delete and insert after the letter Number of calls, written to the article collection of tables. This is roughly what it looks like.

