4

We have a database that uses a LONGTEXT type for a column that does not need that much space. The maximum row size currently has about 6500 characters and that's with a few million inserts so it's very unlikely we will ever need more space. Is it worthwhile to try to reduce the allocated size of this column? Will we see performance improvements?

We are using MySQL 5.6 with InnoDB.

Thanks!

1
  • It depends on a lot of aspects. What is the character set of that column? What row format are you using? (You can check row format with: show table status like 'table_name') Apr 26, 2016 at 7:25

2 Answers 2

3

Let's do the math.

  • 6500 characters, even in CHARACTER SET utf8mb4 takes no more than 26000 bytes.
  • TEXT has a limit of 64K bytes and needs a hidden 2-byte length field.
  • LONGTEXT has a 4-byte length field.
  • Let's say (for the 'math') that the average row length, including this text, is 3000 bytes.

Math...

  • Savings of switching from LONGTEXT to TEXT: 2 bytes per row
  • Percentage space savings: 2/3000 = 0.06%
  • Performance improvement: Less than that.

Conclusion... We have spent much more effort than this minuscule improvement is worth. (But you probably learned some details.)

4
  • afair length is 2 bytes even for LONGTEXT. It's a length of a chunk in a page which is < 16k. so no reason to switch to TEXT at all.
    – akuzminsky
    Apr 27, 2016 at 18:38
  • @akuzminsky - Yes, it is chunked, but I think the total length is stored in the record.
    – Rick James
    May 26, 2019 at 18:37
  • Can you show where ?
    – akuzminsky
    May 26, 2019 at 20:07
  • @akuzminsky - A guess: In the 20-byte structure that lives in the record to point to the chain of off-record chunks for big TEXTs/BLOBs. On the other hand, watching Innodb_buffer_pool_read_requests makes me wonder if LENGTH() does read the chunks.
    – Rick James
    May 26, 2019 at 20:19
1

I do not think it will be worthwhile to change the type. You may save a byte or two for the length field of each column value if using TEXT or MEDIUMTEXT, but that will be insignificant compared to the size of your data.

VARCHAR and TEXT columns are handled the same way in InnoDB, so there is no reason to switch to VARCHAR either.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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