CODEWITHSUNDEEP
sqlmariadb
Luke101
Luke101

Reputation: 65258

What is the max number of columns per table for mariadb

Hi I would like to know the maximum amount of columns allowed per table for the different storage engines and the max row size. I searched the mariadb website documentation and could not find the information. Thank you

Upvotes: 2

Views: 7828

Answers (2)

Bill Karwin
Bill Karwin

Reputation: 562348

MariaDB in its current form is still close enough to MySQL that the same limits apply. The MariaDB fork may diverge further as time goes on.

The actual answer for the maximum number of columns per table in MySQL is complex, because of differences in data types, storage engines, and metadata storage. Sorry there's not just a simple answer.

As @Manquer cites, there's an absolute limit of 64KB per row, but BLOB/TEXT columns don't count toward this limit.

InnoDB pages must fit at least two rows per page, and a page is 16KB minus some header information. So regardless of number of columns, the row must be about 8000 bytes. But VARCHAR/BLOB/TEXT columns can overflow to additional pages in interesting ways. See http://www.mysqlperformanceblog.com/2010/02/09/blob-storage-in-innodb/ for the details.

But there are even further restrictions, based on the .FRM metadata file that applies to all storage engines. It gets really complex, read http://www.mysqlperformanceblog.com/2013/04/08/understanding-the-maximum-number-of-columns-in-a-mysql-table/ for the details and examples about that. There's too much to copy down here.

Given the latter blog article, I was able to design a table that failed at 59 columns.

Upvotes: 4

Manquer
Manquer

Reputation: 7647

MariaDb being originally a fork and drop in replacement for MySQL, mostly follows similar design constraints as MySQL. Although MariaDB documentation does not explicitly say how many columns are allowed.

This number is highly dependent a number of factors including the storage engine used and the way the columns are structured. For InnoDB this is 1,000.

See explanation below from the official documentation (Ref: Column Count Limit)

There is a hard limit of 4096 columns per table, but the effective maximum may be less for a given table. The exact limit depends on several interacting factors.

Every table (regardless of storage engine) has a maximum row size of 65,535 bytes. Storage engines may place additional constraints on this limit, reducing the effective maximum row size.

The maximum row size constrains the number (and possibly size) of columns because the total length of all columns cannot exceed this size.

...

Individual storage engines might impose additional restrictions that limit table column count. 

InnoDB permits up to 1000 columns.

This applies to MariaDb as well

Upvotes: 2

Related Questions