Databases
StackOverflow2013
Postsdbo
POI can store a lot of data (<=4GB) in one table column. But is it a good idea?
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POI can store a lot of data (<=4GB) in one table column. But is it a good idea?
    text
    copied!<p>To make a long story short, one part of the application I'm working on needs to store a somewhat large volume of data in a database, for another part of the application to pick up later on. Normally this would be &lt; 2000 rows, but can occasionally exceed 300,000 rows. The data needs to be temporarily stored and can be deleted afterwards.</p> <p>I've been playing around with various ideas and one thing came to mind today. The <code>LONGTEXT</code> datatype can store a maximum of 2^32 bytes, which equates to 4 GB. Now, that's a lot of stuff to cram into one table row. Mind you, the data would probably not exceed <strong>60-80 MB</strong> at the very most. But my question is, is it a good idea to actually do that?</p> <p>The two solutions I'm currently toying with using are something like this:</p> <ul> <li>Inserting all data as individual rows into a "temporary" table that would be truncated after finish.</li> <li>Inserting all data as a serialized string into a <code>LONGTEXT</code> column in a row that would be deleted after finish.</li> </ul> <p>Purely from a performance perspective, would it be better to store the data as potentially >300,000 individual rows, or as a 60 MB <code>LONGTEXT</code> entry?</p> <p>If it's a wash, I will probably go with the <code>LONGTEXT</code> option, as it would make the part of the application that picks up the data easier to write. It would also tie in better with yet another part, which would increase the overall performance of the application.</p> <p>I would appreciate any thoughts on this.</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload