Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a table with 1699 columns and when I'm trying to insert more columns I get the too many columns in the table error. In this table I have only 1000 rows. For me the most important thing is the number of columns.

Are there any limitations on the table? I want to create 2000 columns. Is that possible?

share|improve this question

migrated from serverfault.com Jul 20 '11 at 13:34

This question came from our site for professional system and network administrators.

11  
Good lord, what the heck for. This smells like an insanely poor database design. Or perhaps you are using the wrong tool for the job. Perhaps you should be looking at database normalization –  Zoredache Jul 20 '11 at 7:27
4  
Rotate your monitor 90 degrees. More seriously, MySQL (or almost any other RDBMS) is not designed for THAT many columns. –  Janne Pikkarainen Jul 20 '11 at 7:29
1  
It probably does not, would you please tell us why anyone would ever think that these amounts of columns is a good way to go? –  3molo Jul 20 '11 at 7:30
7  
And why should 2000 sensors lead to 2000 columns? Redesign your database. Create a separate sensor table or something, but DO NOT add each sensor as a new column. That's just unbelievably wrong thing to do. –  Janne Pikkarainen Jul 20 '11 at 7:35
3  
Maximum table number ... whoa there! You'll likely need only couple of tables. Don't even consider creating 2000 tables instead of 2000 columns! –  Janne Pikkarainen Jul 20 '11 at 7:45
show 7 more comments

6 Answers 6

up vote 4 down vote accepted

Why would you need to create a table with even 20 columns, let alone 2000 ???

Granted, denormalized data can prevent having to do JOINs to retrieve many columns of data. However, if you have over 10 columns, you should stop and think about what would happen under the hood during data retrieval.

If a 2000 column table undergoes SELECT * FROM ... WHERE, you would generate large temp tables during the processing, fetching columns that are unnecessary, and creating many scenarios where communication packets (max_allowed_packet) would be pushed to the brink on every query.

In my earlier days as a developer, I worked at a company back in 1995 where DB2 was the main RDBMS. The company had a single table that had 270 columns, dozens of indexes, and had performance issues retrieving data. They contacted IBM and had consultants look over the architecture of their system, including this one monolithic table. The company was told "If you do not normalize this table in the next 2 years, DB2 will fail on queries doing Stage2 Processing (any queries requiring sorting on non-indexed columns)." This was told to a multi-trillion dollar company, to normalize a 270 column table. How much more so a 2000 column table.

In terms of mysql, you would have to compensate for such bad design by setting options comparable to DB2 Stage2 Processing. In this case, those options would be

Tweeking these settings to make up for the presence of dozens, let alone hundreds, of columns works well if you have TBs of RAM.

This problem multiplies geometrically if you use InnoDB as you will have to deal with MVCC (Multiversion Concurrency Control) trying to protect tons of columns with each SELECT, UPDATE and DELETE through transaction isolation.

CONCLUSION

There is no substitute or band-aid that can make up for bad design. Please, for your sake of your sanity in the future, normalize that table today !!!

share|improve this answer
add comment

I'm having trouble imagining anything where the data model could legitimately contain 2000 columns in a properly normalised table.

My guess is that you're probably doing some sort of "fill in the blanks" denormalised schema, where you're actually storing all different sorts of data in the one table, and instead of breaking the data out into separate tables and making relations, you've got various fields that record what "type" of data is stored in a given row, and 90% of your fields are NULL. Even then, though, to want to get to 2000 columns... yikes.

The solution to your problem is to rethink your data model. If you're storing a great pile of key/value data that's associated with a given record, why not model it that way? Something like:

CREATE TABLE master (
    id INT PRIMARY KEY AUTO_INCREMENT,
    <fields that really do relate to the
    master records on a 1-to-1 basis>
);

CREATE TABLE sensor_readings (
    id INT PRIMARY KEY AUTO_INCREMENT,
    master_id INT NOT NULL,   -- The id of the record in the
                              -- master table this field belongs to
    sensor_id INT NOT NULL,
    value VARCHAR(255)
);

CREATE TABLE sensors (
    id INT PRIMARY KEY AUTO_INCREMENT,
    <fields relating to sensors>
);

Then to get all of the sensor entries associated with a given "master" record, you can just SELECT sensor_id,value FROM sensor_readings WHERE master_id=<some master ID>. If you need to get the data for a record in the master table along with all of the sensor data for that record, you can use a join:

SELECT master.*,sensor_readings.sensor_id,sensor_readings.value
FROM master INNER JOIN sensor_readings on master.id=sensor_readings.master_id
WHERE master.id=<some ID>

And then further joins if you need details of what each sensor is.

share|improve this answer
2  
With the rep you're getting for this (and will no doubt continue to get) perhaps you can now spare some points for those downvotes you've been holding back on. ;) –  John Gardeniers Jul 20 '11 at 8:21
    
I CRAVES THE REP... PRECIOUSSSSSS REP... (I've hit the rep cap for today anyway) –  womble Jul 20 '11 at 8:51
    
@womble - presumably the "2000 sensor fields" are "fields that really do relate to the master records on a 1-to-1 basis" –  Jack Douglas Jul 20 '11 at 15:44
    
@Jack: No, they don't. "master record" has-many sensors, and sensor has-many sensor readings. –  womble Jul 31 '11 at 2:45
1  
I'm happy to make edits but I can't read your mind. You said "'master record' has-many sensors, and sensor has-many sensor readings", and that is not what you have modelled. What you have modelled makes perfect sense and is what others have suggested too. If you read my answer and the comments below it you will see my reasons for suggesting that depending on the nature of the sensors, vertical partitioning may be better. It is possible for example that each sensor has a different data type or domain, and needs to be constrained. –  Jack Douglas Aug 1 '11 at 6:42
show 5 more comments

MySQL 5.0 Column-Count Limits (emphasis added):

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. Examples:

  • InnoDB permits up to 1000 columns.
share|improve this answer
    
@DTest - I expanded the answer to include the relevant text. Ig_, the link may answer the question, but Stack Exchange is not a link farm. In the future please quote or summarize your links so people can quickly see how they answer the question. –  Nick Chammas Jun 16 '12 at 19:23
add comment

It's a measurement system with 2000 sensors

Ignore all the comments shouting about normalization - what you are asking for could be sensible database design (in an ideal world) and perfectly well normalized, it is just very unusual, and as pointed out elsewhere RDBMSs are usually simply not designed for this many columns.

Although you are not hitting the MySQL hard limit, one of the other factors mentioned in the link is probably preventing you from going higher

As others suggest, you could work around this limitation by having a child table with id, sensor_id, sensor_value, or more simply, you could create a second table to contain just the columns that will not fit in the first (and use the same PK)

share|improve this answer
    
This is true. When handling data and corresponding SQL with great care, your answer stands out even more !!! –  RolandoMySQLDBA Jul 20 '11 at 16:31
1  
Using a child table is not a "workaround". Having one column for each sensor is simply bad (wrong) design. That is like having one column for every employee in an HR system, or one column for every car manufacturer for a DB that manages car models. –  a_horse_with_no_name Jul 21 '11 at 6:46
3  
@a_horse - you are making assumptions that I doubt are valid. It is quite possible that the number of sensors is basically fixed, that all are read simultaneously and that all return data every time. In which case one column per sensor is not "wrong", merely impractical given the limitations of the database. I like to assume questioners are not idiots until proven otherwise and iUngi has responded with dignity in the face of very unhelpful responses from the SF crowd. –  Jack Douglas Jul 21 '11 at 8:39
1  
@Jack Douglas: even if all those assumptions of yours were true (which I highly doubt) storing each sensor value in its own column will cause trouble in the long run. What about queries like "what's the average value for sensors 10 to 50 and 25 to 100 between yesterday and today"? or "Which sensor had the highest reading value last monday?". Try to write queries for this with 2000 columns. Using a normalized table will solve more problems in the long run than the 2000 columns solution will solve now. –  a_horse_with_no_name Jul 21 '11 at 8:56
1  
Sure, if the sensors are storing related values - I am assuming they are unrelated (eg they are all measuring different kinds of things rather than basically the same thing at different locations). You might doubt that but only the OP knows for sure - and it is not impossible in medical or scientific fields. –  Jack Douglas Jul 21 '11 at 10:29
show 2 more comments

First some more flaming, then a real solution...

I mostly agree with the flames already thrown at you.

I disagree with key-value normalization. Queries end up being horrible; performance even worse.

One 'simple' way to avoid the immediate problem (limitation of number of columns) is to 'vertically partition' the data. Have, say, 5 tables with 400 columns each. They would all have the same primary key, except one might have it being AUTO_INCREMENT.

Perhaps better would be to decide on the dozen fields that are most important, put them into the 'main' table. Then group the sensors in some logical way and put them into several parallel tables. With the proper grouping, you might not have to JOIN all the tables all the time.

Are you indexing any of the values? Do you need to search on them? Probably you search on datetime?

If you need to index lots of columns -- punt.

If you need to index a few -- put them into the 'main table.

Here's the real solution (if it applies)...

If you don't need the vast array of sensors indexed, then don't make columns! Yes, you heard me. Instead, collect them into JSON, compress the JSON, store it into a BLOB field. You will save a ton of space; you will have only one table, with not column limit problems; etc. Your application will uncompress, and then use the JSON as a structure. Guess what? You can have structure -- you can group the sensors into arrays, multilevel stuff, etc., just like your app would like. Another 'feature' -- it is open-ended. If you add more sensors, you don't need to ALTER the table. JSON if flexible that way.

(Compression is optional; if your dataset is huge, it will help with disk space, hence overall performance.)

share|improve this answer
1  
+1 but I think the blob solution is a long shot - it's pretty likely iUngi will want to be querying that data with SQL don't you think? –  Jack Douglas Jul 21 '11 at 8:46
1  
5 tables with 400 columns each? How on earth can that be better than adding a single column named 'sensor' and some indexes? –  reinierpost Mar 14 '12 at 16:27
add comment

Nothing to say except shit happens, I guess.

If you'd rotate the table (putting the sets as columns and the columns as sets) you'd have a couple hundred sets left.. :)) )

Seriously: It's never really a good idea to have a table with an dynamic structure, even if it might seem a good idea at the beginning and performance is not the issue. If it's called the same (e.g. sensor) that should be the name of that column. If types differ, it's usually not called the same. The same with JSON-data in the db (a blessing as it is), except if you're absolutely sure you never need to query any column for any reasons (SELECT/UPDATE) individually. Which I assume with sensors - working at their own time and probably having their own threats somewhere - is not the case. Pushing the problem by creating more not-normalized tables (400x5) will not solve it as well. Just making the programming more complicated and you crazy at some point for having found a workaround to a solution that just wasn't a good idea from the beginning.

I'm sure there is a way to have 1,699,000 sets in a table with 2 columns. I don't want to know what that does to your app, but that's the way to go.

Good luck!

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.