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:
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:
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:
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:
Which is curiously gone from MySQL 5.6, it’s replaced by the following set of defines
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):
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!
Learn more about Percona Server for MySQL
I got it to fail at 59 columns.
The maximum column comment length was increased to 1023 in MySQL 5.5, so I modified your script to create comments of that length instead of 255 as you have.
and one more question “how many tables (MYISAM or INNODB) mysqld can create, the maximum number ?”
Try the sql-bench/crash-me.sh script in the MySQL Server source repo, it suposed to have lot’s of different test for different limitations… it you can get it to work.
The only thing I can say here is “what the ****”. I can’t imagine many situations where you’d need a couple of thousand columns, but the question still remains why such archaic things are still in play. You definitely picked the right way for Drizzle, imho.
I assume these limits also apply to views in MySQL?
—-
(This post was edited to remove profanity.)
Bill, I tried with 5.1 – I suppose I should have specified, or at least tried with 5.5 or 5.6 too 🙂
59 is an amazing result though – that’s brilliant.
Johan, I’m actually not sure about VIEWs… VIEWs are stored in a “text” FRM, which is basically a textual representation of the SQL query that is the view (along with some key/value metadata)….