Vertical table partitioning in MySQL

Question

Another question.

Is it better to use the vertically partition wide table (in my case, I’m thinking about separating the login details from the user’s address, personal data, etc.) at the design stage or is it better to leave it and break it after the data and do profiling ?

The answer seems obvious, but I'm worried that a line splitting the table, once down the line, would mean extra work on rewriting the user model +, it seems reasonable to separate the frequently used login data from more static personal data.

Does anyone have some experience supporting recommendations on how to proceed :)? Thanks in advance.

Answer 1

Premature optimization is ...

Separating columns into another table has disadvantages:

• Some operations requiring a single request require two requests or a combination
• This is not trivial to ensure that each row in each table must have a corresponding row in another. Thus, you may run into integrity issues.

On the other hand, at best, it is doubtful that this will improve performance. If you cannot prove this in advance (and creating a ten millionth record table with random data and doing some queries is trivial), I would not do that. Valid Doug Kress suggestions for encapsulation and SELECT * exclusion.

The only reason this is done is because your single table design does not normalize, and normalization involves splitting the table.

Answer 2

I believe that it would be better to save it as a separate table, but to encapsulate your access to data as much as possible so that later it is easy to reorganize.

When you access data, be sure to collect the necessary information in the query (avoid "SELECT *").

Having said that, make sure that the data stored in the table is normalized accordingly. You may find that you want to save multiple addresses for the user, for example, in which case you should put it in a separate table.