Performance Zone is brought to you in partnership with:

I specialise MySQL Server performance as well as in performance of application stacks using MySQL, especially LAMP. Web sites handling millions of visitors a day dealing with terabytes of data and hundreds of servers is king of applications I love the most. Peter is a DZone MVB and is not an employee of DZone and has posted 192 posts at DZone. You can read more from them at their website. View Full User Profile

Understanding the maximum number of columns in a MySQL table

04.09.2013
| 1399 views |
  • submit to reddit

 This post comes from at the MySQL Performance Blog.

This post was initially going to be two sets of polls: “What is the maximum number of columns in MySQL?” and “What is the minimum maximum number of columns in MySQL?”. Before you read on, ponder those questions and come up with your own answers… and see if you’re right or can prove me wrong!

Back in 2009, I finished what seemed an epic task in the Drizzle code base: banishing the FRM file. Why? We felt it was not a good idea to keep arbitrary and obscure limitations from the 1980s alive in the 21st century and instead wanted a modular system where the storage engines themselves owned their own metadata. This was a radical departure from the MySQL philosophy, and one that has truly paid off in the Drizzle code base. However… for those using MySQL, Percona Server, MariaDB or any other of the MySQL branches/forks, you get to have these odd limitations.

Why do I discuss the FRM file? If we look at the MAX_FIELDS define in MySQL, we’ll see that it’s defined as 4096. This, however, is not the true limit. To find out what the actual limit is, we must delve further into the FRM file.

What is an FRM file? It’s the FoRM file from UNIREG. It’s FRM rather than FORM as, well, you used to only get three characters after a dot in a file name. Back in 1979, Monty developed an in-house database named UNIREG. UNIREG was a text-based program for entering records into an ISAM-style database. It would have an 80×24 text UI for entering data and a separate UI for generating reports. This evolved into the SQL based MySQL, with MySQL 1.0 being released in 1995.

The FoRM file specified what fields where on what screen for entering, as well as any constraints as to what could be entered. For example, if you had more than a certain number of fields, you were going to need more than one 80×24 screen to enter in all the data! You could also have things like NEXT_NUMBER fields (what we know today as auto_increment), CASEUP and CASEDN fields which although not implemented in MySQL, the defines can still found in the source. Basically, it’s why we can’t have nice things (like default values other than NOW()).

It also has certain limits which by any modern standard are purely arbitrary. One of those is the limit that a certain part of the FRM file cannot be greater than 64kb. The bit of code in question that comes into play around the maximum number of columns is this:

/* Hack to avoid bugs with small static rows in MySQL */
reclength=max(file->min_record_length(table_options),reclength);
if (info_length+(ulong) create_fields.elements*FCOMP+288+
    n_length+int_length+com_length > 65535L || int_count > 255)
{
  my_message(ER_TOO_MANY_FIELDS, ER(ER_TOO_MANY_FIELDS), MYF(0));
  DBUG_RETURN(1);
}

Which is, of course, obvious! Various parts of this limit are:

  • info_length is roughly 2+strlen(field_name) for each field. Unless you have many columns, and then it’s something else (as in that case you don’t get your 80×24 terminal UI in your FRM file, you instead get some bunch of bytes per 19 columns).
  • create_fields.elements*FCOMP is just number of fields multiplied by 17
  • 288 is static and is always needed
  • int_length is the interval length. This isn’t the normal meaning of the word interval, we can only guess that it’s called this due to either something UNIREG specific or it’s just Monty’s English language skills in the 1980s. We’ll come back to this one.
  • com_length is the length of all the comments for each field (but not the table)

An interval in UNIREG speak is a set of strings that are the options for ENUM or SET columns. The tricky bit is that it’s unique intervals, not actual intervals, so two ENUM columns both having the options ‘Y’ and ‘N’ will use less space in the FRM than if you had one with ‘Y’ and ‘N’ and the other with ‘A’ and ‘B’.

If you noticed that if you have a long comment on each field you reduce the number of different ENUM elements you can have, you are correct. There is also a limit of 255 unique intervals, so while you can have many more ENUM(‘Y’,'N’) columns, you can only have 255 ENUM columns with unique values.

If you were looking for a very basic formula that is mostly accurate, I present this:

    foreach field:  17+2*(strlen(field_name)+2) (bytes)
    + length of all comments (in bytes)
    + length of all intervals (for ENUM, SET) in bytes.

If you use that as a rule of thumb, with that not being able to exceed 64k, you’re roughly on the right track to working out the maximum number of columns in a MySQL table.

So what’s the maximum number of columns in a MySQL table? Well.. I tried a few things before I settled on the following (perl) program (accepts command line parameter of number of columns to create) to produce the CREATE TABLE sql statement:

sub cname ($) {
  my $c=shift;
  my $name="";
  while($c > 0)
  {
    my $n=$c%36;
    $name.=chr(ord('0')+$n) if $n < 10;     $name.=chr(ord('a')+($n-10)) if $n >= 10;
    $c= int $c/36;
  }
  return $name
}
my $sql= "CREATE TABLE t (";
 foreach(1..shift @ARGV) {
    my $n=cname($_);
    $sql.="`$n`";
    $sql.=" ENUM('Y','N','M','0','1','2')\n";
 }
 chop $sql;
 chop $sql;
 $sql.=");";
 print $sql;

This gets you a 46kb CREATE TABLE statement and a 76kb FRM file for a table with 2,829 columns. I believe this to be the maximum number of columns you can create.

If you try setting the storage engine to InnoDB however, you will get an error message! The exact error message you get is not very interesting and just says “Can’t create table ‘test.t’ (errno: 139)”. This is because InnoDB has a hard limit of 1,000 columns. This is the code from ha_innodb.cc that enforces that limit:

if (form->s->fields > 1000) {
  /* The limit probably should be REC_MAX_N_FIELDS - 3 = 1020,
  but we play safe here */
 
  DBUG_RETURN(HA_ERR_TO_BIG_ROW);
}

Which is curiously gone from MySQL 5.6, it’s replaced by the following set of defines

#define	DATA_N_SYS_COLS 3 /* data0type.h */
/* from rem0types.h */
#define REC_MAX_N_FIELDS        (1024 - 1)
#define REC_MAX_N_USER_FIELDS	(REC_MAX_N_FIELDS - DATA_N_SYS_COLS * 2)

Which adds up to (1024-1)-3*2=1017 which is exactly the same as what I can create. That’s right folks, in MySQL 5.6 you can create a table with a few more columns in InnoDB!

This led me on another idea… what is the minimum maximum number of columns you can create? You may think that it is 255 based on the limit of the number of intervals above, but can you get any lower? Why yes you can! With this bit of perl code I was able to hit a too many columns error with only 192 columns (i.e. 191 worked):

 sub cname ($$) {
     my $c=shift;
     my $name="";
     while($c > 0)
     {
	 my $n=$c%36;
	 $name.=chr(ord('0')+$n) if $n < 10; 	 $name.=chr(ord('a')+($n-10)) if $n >= 10;
	 $c= int $c/36;
     }
     $name.='0' foreach(length $name .. shift);
     return $name
 }
 my $sql= "CREATE TABLE `".cname(16,63)."` (";
 foreach(1..shift @ARGV) {
     my $n=cname($_,63);
     $sql.="`$n`";
     $sql.=" ENUM('".cname(0,64)."') COMMENT '".cname($_,254)."',\n";
 }
 chop $sql;
 chop $sql;
 $sql.=");";
 print $sql;

So the maximum number of columns for a table in MySQL is somewhere between 191 and 2829, depending on a number of factors. I’d be interested to hear if you’ve been able to beat my minimum/maximums!



Published at DZone with permission of Peter Zaitsev, author and DZone MVB. (source)

(Note: Opinions expressed in this article and its replies are the opinions of their respective authors and not those of DZone, Inc.)