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)