MySQL Forums
Forum List  »  InnoDB

Simulating wide (1000 column tables) with InnoDB
Posted by: Peter Smith
Date: January 11, 2018 10:57AM

I'm interested in discovering the best approach to increasing the maximum number of columns in a MySQL (with InnoDB) table. Thanks to the excellent MySQL docs, I learned there's hard limit on the number of columns, and it's dependent on both the database block size, as well as the data types of those columns. This is the error I was seeing a lot of...

ERROR 1118 (42000) at line 2: Row size too large (> 8126). Changing some columns to TEXT or BLOB may help. In current row format, BLOB prefix of 0 bytes is stored inline.

I fully understand this error now, but my goal is to figure out the best way to _simulate_ wide tables (albeit with slower performance). I appreciate that normalized tables are usually the best approach, but our product allows our non-technical customers to define arbitrary-width tables with arbitrary column types, so we need to do something automatic behind the scenes.

I've done some performance tests of joining together multiple 100-column tables to simulate a 1000-column table, and it works pretty well (especially if we only join the sub-tables we actually need for each query). I've also tried using JSON columns to create "virtual" columns, but the performance is pretty bad for our workloads.

My question: Has anybody found other solutions that work for them?

In case you're interested, here are the results of my performance tests. I'm hoping there might be better solutions I haven't thought of.

https://medium.com/build-acl/the-maximum-width-of-mysql-database-tables-76aa181b1441
https://medium.com/build-acl/supporting-wide-tables-in-mysql-18248e4e3b0a

Thanks,

Peter Smith

Principal Software Engineer, ACL (www.acl.com)

Options: ReplyQuote


Subject
Views
Written By
Posted
Simulating wide (1000 column tables) with InnoDB
132
January 11, 2018 10:57AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.