Does it make sense to separate the tables separately to separate frequently used data from rarely received data?

Question

Let's say I had a table with 60 odd columns, but in 99% of the cases, I selected only 3 or 4 of them. Does it make sense to split the table into one table with four columns and one 56 columns from 1 to 1, the correspondence between the rows. Will it save me? Is there a performance difference between a sample of 1000 rows of 4 columns from a table of 4 columns and a sample of 1000 columns of four columns from a table of columns 60?

I am using "MySQL 14.14. Distribution 5.1.49 for debian-linux-gnu"

Answer 1

In other DBMSs, you can achieve this by vertical partitioning. Having this opportunity, you can divide your table into several sections vertically - this means the separation by columns.

This has advantages over doing it manually how you want to do it. It does not destroy your table design and is transparent to the programmer who writes SQL for these tables. The work is done at a lower level and at that good.

I would wait if you really need this extra performance. And maybe MySQL supports vertical splitting for one or more days. Do not destroy the table for this unless you really need it.

Answer 2

It depends on the performance of your database. If you have a high cache hit ratio, so your table is cached, then it makes no sense to split the table. Only if for many of the databases selected from the table it is necessary to read data from disks, then this can be a good idea.