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.htmland|TEXT| https://dev.mysql.com/doc/refman/5.0/en/blob.htmlcolumns 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
Re: longtext fields in a row
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.htmland|TEXT| https://dev.mysql.com/doc/refman/5.0/en/blob.htmlcolumns 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 -- Shawn Green MySQL Senior Principal Technical Support Engineer Oracle USA, Inc. - Hardware and Software, Engineered to Work Together. Office: Blountville, TN You or someone you know could be a presenter at Oracle Open World! The call for proposals is open until April 29. https://www.oracle.com/openworld/call-for-proposals.html On 4/1/15 10:35 AM, Andrew Mueller wrote: There is a max row size of 65,535 bytes. There is no real way to get around this limit other than placing the HTML code somewhere else, perhaps in a different table. On Wednesday, April 1, 2015, Trianon 33 triano...@gmail.com wrote: Hello, I'm fiddling wit a table where I put in a date field (datetime, also key) and some integer fields (8 of them mostly 14 long) and some longtext fields (16 of them). The longtext fields are filled with some statistics I generate complete with HTML around, something like this: td12.925.965/td but than bigger, but mostly smaller than 1 Mb. This row is initially created by filling the first 10 fields (datetime, the integer and the 1st longtext) and than updated each and every time the next longtext value available is. However this is ok up to the 10th longtext field and than it stops. The next longtext operations runs ok, no errormessages etc. but the longtext field itself remains empty. Up to now I have no clue about my wrongdoings, so do you have any suggestions? Is there a max of longtext fields in 1 row? Is there a max of longtext size in 1 row? Other idea's? Thanks in advance for any advice, best regards, Schimanski. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
longtext fields in a row
Hello, I'm fiddling wit a table where I put in a date field (datetime, also key) and some integer fields (8 of them mostly 14 long) and some longtext fields (16 of them). The longtext fields are filled with some statistics I generate complete with HTML around, something like this: td12.925.965/td but than bigger, but mostly smaller than 1 Mb. This row is initially created by filling the first 10 fields (datetime, the integer and the 1st longtext) and than updated each and every time the next longtext value available is. However this is ok up to the 10th longtext field and than it stops. The next longtext operations runs ok, no errormessages etc. but the longtext field itself remains empty. Up to now I have no clue about my wrongdoings, so do you have any suggestions? Is there a max of longtext fields in 1 row? Is there a max of longtext size in 1 row? Other idea's? Thanks in advance for any advice, best regards, Schimanski. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: longtext fields in a row
Ok, thanks, couldn't find that anywhere. Need to think for a different solution.. Anyway thanks for the remark, best regards, Schimanski. Andrew Mueller schreef op 01-04-15 om 19:35: There is a max row size of 65,535 bytes. There is no real way to get around this limit other than placing the HTML code somewhere else, perhaps in a different table. On Wednesday, April 1, 2015, Trianon 33 triano...@gmail.com mailto:triano...@gmail.com wrote: Hello, I'm fiddling wit a table where I put in a date field (datetime, also key) and some integer fields (8 of them mostly 14 long) and some longtext fields (16 of them). The longtext fields are filled with some statistics I generate complete with HTML around, something like this: td12.925.965/td but than bigger, but mostly smaller than 1 Mb. This row is initially created by filling the first 10 fields (datetime, the integer and the 1st longtext) and than updated each and every time the next longtext value available is. However this is ok up to the 10th longtext field and than it stops. The next longtext operations runs ok, no errormessages etc. but the longtext field itself remains empty. Up to now I have no clue about my wrongdoings, so do you have any suggestions? Is there a max of longtext fields in 1 row? Is there a max of longtext size in 1 row? Other idea's? Thanks in advance for any advice, best regards, Schimanski. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql -- /Andrew R. Mueller /603-748-8449
Re: longtext fields in a row
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.htmland|TEXT| https://dev.mysql.com/doc/refman/5.0/en/blob.htmlcolumns 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... On 4/1/15 10:35 AM, Andrew Mueller wrote: There is a max row size of 65,535 bytes. There is no real way to get around this limit other than placing the HTML code somewhere else, perhaps in a different table. On Wednesday, April 1, 2015, Trianon 33 triano...@gmail.com wrote: Hello, I'm fiddling wit a table where I put in a date field (datetime, also key) and some integer fields (8 of them mostly 14 long) and some longtext fields (16 of them). The longtext fields are filled with some statistics I generate complete with HTML around, something like this: td12.925.965/td but than bigger, but mostly smaller than 1 Mb. This row is initially created by filling the first 10 fields (datetime, the integer and the 1st longtext) and than updated each and every time the next longtext value available is. However this is ok up to the 10th longtext field and than it stops. The next longtext operations runs ok, no errormessages etc. but the longtext field itself remains empty. Up to now I have no clue about my wrongdoings, so do you have any suggestions? Is there a max of longtext fields in 1 row? Is there a max of longtext size in 1 row? Other idea's? Thanks in advance for any advice, best regards, Schimanski. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: longtext fields in a row
There is a max row size of 65,535 bytes. There is no real way to get around this limit other than placing the HTML code somewhere else, perhaps in a different table. On Wednesday, April 1, 2015, Trianon 33 triano...@gmail.com wrote: Hello, I'm fiddling wit a table where I put in a date field (datetime, also key) and some integer fields (8 of them mostly 14 long) and some longtext fields (16 of them). The longtext fields are filled with some statistics I generate complete with HTML around, something like this: td12.925.965/td but than bigger, but mostly smaller than 1 Mb. This row is initially created by filling the first 10 fields (datetime, the integer and the 1st longtext) and than updated each and every time the next longtext value available is. However this is ok up to the 10th longtext field and than it stops. The next longtext operations runs ok, no errormessages etc. but the longtext field itself remains empty. Up to now I have no clue about my wrongdoings, so do you have any suggestions? Is there a max of longtext fields in 1 row? Is there a max of longtext size in 1 row? Other idea's? Thanks in advance for any advice, best regards, Schimanski. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- /Andrew R. Mueller /603-748-8449
