SQL max size

[EricOnAdventure]

Hey guys, I am trying to put dynamically generated text into a database. The issue is the text can be 500 words+ when you include the HTML wrapping. I am not sure a database can hold it. I tried...

CREATE TABLE `ericgonzp`.`3RLresult` (
`id` INT(4) NOT NULL AUTO_INCREMENT ,
`userhash` VARCHAR(50) NOT NULL ,
`rl1ready` VARCHAR(3) NOT NULL ,
`rl2ready` VARCHAR(3) NOT NULL ,
`rl3ready` VARCHAR(3) NOT NULL , 
`rl1result` VARCHAR(8000) NOT NULL ,
`rl2result` VARCHAR(8000) NOT NULL ,
`rl3result` VARCHAR(8000) NOT NULL ,                                      
 PRIMARY KEY (`id`)) ENGINE = InnoDB;

Sadly this doesn't work...

 

#1118 - Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs

 

Any idea what I should do?

[PravinS]

Instead of VARCHAR data type for text use TINYTEXT, TEXT, MEDIUMTEXT, or LONGTEXT according to your requirement

[Barand]

And normalize your tables.

 

When you have fields named xxx1, xxx2, xxx3, ..., xxxN, you are doing it wrong.

[kicken]

If you want to store a large amount of text in a column, then you'd do as the error suggests and use the TEXT data type. This type stores the text in a separate location from the row and then just stores a pointer to it within the row itself.

 

That said, you could also re-evaluate how big you need the columns to be. For example if your 500 word block of text had an average word length of 6 characters, you'd be using only about 3,500 characters total meaning you could make your columns 4,000 characters long rather than 8,000.

 

You could also re-design your DB so you have more rows but less columns. For example:

create table result (
 id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
 , userhash VARCHAR(50) NOT NULL
 , resultNumber INT
 , ready VARCHAR(3) NOT NULL
 , result VARCHAR(8000) NOT NULL
)

Instead of 3 ready/result columns, you have 3 rows, each with a single ready/result column plus a column indicating the result number (1, 2, 3...). This is known as normalization and is generally a good idea. If you find yourself having a bunch of numbered columns in a table, that is a good sign you should re-think that design.

 

Think about your design now. What happens if next month you need to add a 4th result? You'd have to add more columns, which could cause all kinds of problems. All the existing rows would have to be expanded to include the new columns. Your queries may break if you are doing something like SELECT * due to the new columns. You might fail to add the columns do to the row size limit you already discovered.

 

With the alternate row based design you just add another row with resultNumber=4, no problems at all. There's no limit to the number of results you could potentially have with the alternate design. If you want to have a limit, you enforce it in your code. If you need to raise the limit later, it's a simple code change, not a whole database refactor.

[EricOnAdventure]

thanks guys, great suggestions and exactly what I needed