Web lists-archives.com

Re: longtext fields in a row




All,

Unfortunatelu this didn't do the trick.

I changed (with PHPMYADMIN) all fields from LONGTEXT to TEXT, all other parameters furthermore unchanged.

Ran the same procedure again, but stopped updating the row at exact the same spot. How come? No idea.

What I'm trying to achiev is this:

I maintain a database with rougly 13 million (13.000.000) records. Since some statistics consume too much time for an on-line environment, I generate them during the night.

What I do is count some things, which go into the INT fields, but I also make some (smaller and bigger) tables which I generate including the HTML codes like <td></td> enclosed. These last ones are going into the TEXT fields.

As long as I want to put in a DB table, do I have any other options than a TEXT field. The external storage outside the table doesn't matter for me. Apart from not working now, it was a good solution, but I don't see other solutions or I'm looking over it.

Any advice appreciated, best regards, Schimanski.


shawn l.green schreef op 01-04-15 om 20:22:
Hi Andrew,

On 4/1/2015 1:58 PM, Andrew Wallace wrote:
I thought that TEXT fields only stored a pointer to the actual data in
the table,
not the data itself - storing 9 to 12 bytes in the table:

|"BLOB| <https://dev.mysql.com/doc/refman/5.0/en/blob.html>and|TEXT|
<https://dev.mysql.com/doc/refman/5.0/en/blob.html>columns count from
one to four plus eight bytes each toward the row-size limit because
their contents are stored separately from the rest of the row."
   -- https://dev.mysql.com/doc/refman/5.0/en/column-count-limit.html

also: https://dev.mysql.com/doc/refman/5.0/en/storage-requirements.html

That said, I would think a better structure would be to have the data
stored in a different
table, keyed to a table containing the date and integer fields...


Well, that also depends on which row_format he has told his InnoDB engine to use for that table. If he uses DYNAMIC then the BLOB/TEXT pointer is only 20 bytes and none of it is stored in the base row. Using the older settings the first 254 characters are actually part of the row to save the extra jump to the off-page storage for the remainder of the BLOB/TEXT
http://dev.mysql.com/doc/refman/5.6/en/innodb-row-format-dynamic.html



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql