CODEWITHSUNDEEP
mysqljoinpartitioning
Hassan Farid
Hassan Farid

Reputation: 113

Vertical Partition MySQL table

I am reviewing a case, where I have 200+ columns mostly varchar(100). The columns are from several external data sources like CRM / Demographics data etc. I cannot hold them in one MySQL table with ever growing number of columns.

The general query situation may contain columns from one or more vertical partitions.

MySQL version: 5.7 Storage Engine: InnoDB

Upvotes: 1

Views: 374

Answers (1)

Rick James
Rick James

Reputation: 142346

  • If groups of columns are addresses (street, city, state, country, postal_code), you could/should move multiple addresses to a single "Locations" table. (Ditto for other logical groupings.)

  • Are multiple columns really "an array splayed across columns"? Eg, "foo1", foo2", "foo3", ...? If so, the really should not be just vertically partitioned, but turned into multiple rows in the other table.

  • If some columns are really numbers or dates, use the appropriate datatype (after cleansing the input).

  • You say "most are VARCHAR(100)". Make reasonable upper bounds; this will help (some) in avoiding row-size limitation.

  • Are some of the columns "sparse"? That is, most rows do not have entries for those values? Collect then into a single JSON column. (Or a couple of JSON columns, if there is a distinct split.) If you have an old version of MySQL/MariaDB (you don't), simply put the JSON string into a TEXT column.

If you are still stuck with vertical partitioning, the number of columns and number of tables makes very little difference in the JOIN performance. It would be better to look at the SELECTs to decide which columns to put in each partition -- Have all the searching in a single table (a WHERE clause hitting multiple table is often inefficient). Having a partition that is usually unused may let you avoid JOINing to it.

Upvotes: 1

Related Questions