Why should i separate freq used/not used columns in a table?

Question

It has been mentioned to me i should separate columns that are used often from columns that are not. Why?

Is it because a table with less columns is better for the cache? Is there an optimization for caching all columns rather than selected? Is this for tables frequently read? Should I separate freq write columns into their own table?

Answer 1

I have not heard of this, though guess it might make some sense. When a table is retrieved the data must be loaded into memory on the server. If you have columns that are frequently accessed and ones that are in-frequently accessed in the same table then it is conceivable that without well-made queries that all of this would be loaded into memory. If this does happen the amount of data loaded into memory would be decreased by moving the in-frequently accessed data into another table.

I don't know exactly how MySQL does all of its searches on the very base level, but I assume that if you use the names of the columns when selecting and not * then this problem would be minimized.

Again, I don't know the details of how all of this is coded, but I don't think it should be a problem as long as your don't select columns you don't need.

Answer 2

When you update a row, many databases will make a copy of the entire row. Depending on how long the copies of the row persist (which depends on many factors) you may end up with more garbage in the database than if you had separated the row into two tables. This garbage will make the database larger, and therefore may make queries slower.

Whether you want to prematurely optimize by denormalizing (which you would have needed to do to get rows that could benefit from being split) is something you should consider in any case.

Answer 3

This generally has to do with not making the parent table too wide which creates problems in the paging (and possible integrity problems as you might suddenly find you can't store a record because it is wider than the number of bytes the row size will allow.). Read about how the database stores records in pages in your particular database and you will see why less wide tables are generally preferred by experienced designers. One method of creating less wide tables is to move the less used information to a table that has a one-to-one relationship with the initial table.

Answer 4

RL use case would be having a Details view (displaying 1 record at a time with all details) and List view for same items.

The separation for the List view fields is recommended because it allows efficient use of disk cache feature:

• disk cache can read ahead by copying more data than what the program asked for in the assumption that the following consecutive data segment will be needed next.