Vertical partitioning of tables in MySQL

Another question.

Is it better to vertically partition wide table (in my instance I am thinking about splitting login details from address, personal etc. details of the user) on a design stage or better leave it be and partition it after having some data and doing profiling?

Answer seems 开发者_StackOverflow中文版to be obvious but I am concerned that row splitting a table sometime down the line will mean additional work to rewrite the user model + it seems reasonable to split often accessed login details from more static personal details.

Anyone has some experience backed advice on how to proceed :)? Thanks in advance.

Premature optimization is...

Splitting columns off to a different table has drawbacks:

• Some operations that required a single query now require two queries or a join
• It's not trivial to enforce that every row in each table needs to have a corresponding row in the other. Thus, you might face integrity problems

On the other hand, it's dubious at best that doing it will improve performance. Unless you can prove it beforehand (and creating a 10 million records table with random data and running some queries is trivial), I wouldn't do it. Doug Kress' suggestions of encapsulation and avoiding SELECT * are the right way.

The only reason to do it is if your single table design is not normalized and normalization implies breaking up the table.

I believe it would be better to keep it as a single table, but encapsulate your access to the data as much as possible, so that it would be easy to refactor later.

When you do access the data, be sure to only gather the information you need in the query (avoid 'SELECT *').

Having said that, be sure that the data saved with the table is normalized appropriately. You may find that you want to store multiple addresses for a user, for instance - in which case you should put it in a separate table.