Is there a benefit in creating a vertical partition of a table if only relevant columns are being selected anyway?

Question

I am using MySQL 5.6. I have some tables that have upwards of 15-20 columns each.

However, I am being careful to only select relevant columns in each of my queries.

Would there still be some benefit (ex: performance-wise) in creating vertical partitions of these tables?

Answer 1

I am assuming some sort of manual implementation of vertical partitioning. The documentation is quite clear:

MySQL 5.6 does not support vertical partitioning, in which different columns of a table are assigned to different physical partitions. There are not at this time any plans to introduce vertical partitioning into MySQL 5.6.

When you implement it yourself, you are essentially creating multiple tables with the same primary key but different sets of columns. This can be useful under some circumstances:

• There is very little data modification (because this would require figuring out which partition(s) have the columns and also require extensive locking).
• There are a handful of columns that are accessed very, very often without other columns.
• Or, possibly, many rows are missing values for some columns (so the partition table would simply not contain these rows).

In general, this is too much work in a database that does not provide native support. One situation where it can be useful is when there are wide text/blob columns that are rarely accessed. Putting these in a separate table can make queries more efficient by not reading in the data from these columns, when the columns themselves are not being used.

So, there are occasions where such an approach is useful. It is a rather sophisticated optimization, and it is unlikely to be needed for 20-25 columns on a typical table.