CODEWITHSUNDEEP
mysqlvertical-partitioning
prashant
prashant

Reputation: 25

Does mysql support vertical partitioning?

I have a mysql database growing @ 45-50% a year. In last couple of years it has increased to a significant amount in size and now causing performance issues in production.

While analyzing it on test environment I found that there is a table which has around 20 columns with 4-5 columns of type longtext. This has around 1.8M records and is being frequently accessed by application. I have gone through many mysql performance blogs and got a sense that vertical partitioning may help in this case so thought to give it a try with manually breaking the table in two parts with all logntext columns in one table and other columns in another table with both tables mapped by primary key and observed 80% performance improvement for the same query which was causing the issue.

Now, I am not sure if I can achieve vertical partitioning at database level without breaking table manually as if I go with manual option it will require a lot of changes in code and queries which could result in some other surprises on production.

Please suggest if MySql actually supports vertical partitioning at database level. I tried to google it but found examples of horizontal partitioning only, no references to vertical partitioning. Appreciate your help on this.

P.S. I am not a database professional so everything I did is based on suggestions/feedback on MySql blogs/forums.

Thanks

Upvotes: 1

Views: 537

Answers (1)

Szilard K
Szilard K

Reputation: 48

mysql does not support vertical partitioning

https://dev.mysql.com/doc/refman/5.7/en/partitioning-overview.html

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

Upvotes: 1

Related Questions