Should you use adjacent tables for one-to-many , one-to-one relationships?
Is there any difference in performance if, for example, for the products table, in which there should be a code field, I will create a separate products_code table and write products_id and code respectively?
If the relationship is 1: 1, then this technique is called vertical partitioning. It is used to separate the structure of the table vertically for optimization.
Example 1: for example, from the users table, the home address of the user address is rarely read. Then it is possible to divide this table into two, and store the address separately, reading it if necessary. This technique will reduce the size of the users table, and small tables work faster.
Example 2: in the same table there is a last_login field that is updated each time a user logs in. But every time the table is updated, the query cache stops working! By placing this field in a separate table, we will keep users updated to a minimum.
Example 3: the table is huge. ALTER TABLE cannot be done (the operation is very long, simple project). The extra column is rarely used. It makes sense to put it in a separate table. On the other hand, to solve a non-blocking ALTER TABLE problem, there is a pt-online-schema-change
But first, make sure that these separated tables do not have to be merged all the time , because, on the contrary, it can reduce performance.
адрес читается редко - well, never read. Who does he interfere with in the table? About frequent updates I want to add more about the peculiarity of the work of locks. When the updated record to commit is blocked for reading. Example 3, IMHO, smacks of a crutch - Anton Shchyrovпопахивает костылестроением - this is not realistic, I agree. But in reality, ALTER TABLE may take several hours / days, and at this time the table is locked. Do you know how to resolve such situations? If yes, write what you use - Total PusherIf you want it to be correct and do not shower with slippers, bring everything to the third normal form. The concept is clearly mathematically defined, the algorithm is also mathematically defined. "No need to think." Forgive me for not including here the entire definition of forms and the casting algorithm.
According to your question - it is necessary to distinguish One-to – Zero-or-One from one-to-one. One-to – Zero-or-One: If the product may not have a code, then you need a separate table. one-to-one: If each product necessarily has a code, add the code field to the product table.
Source: https://ru.stackoverflow.com/questions/881303/
All Articles