0

I have two columns that can be long, but 99% of times will be short (less than 255 characters). This means that I can not use VARCHAR(255) because 1% of times I could not save data. I thought to make them TEXT type, but I read here https://stackoverflow.com/questions/44493446/mysql-is-it-a-good-practice-to-use-text-datatype-to-nullable-fields that TEXT slow down queries performance on that table.

If I make both columns VARCHAR(5000) instead of TEXT, would it have a negative performance impact on queries on that table anyway?

3
  • Can you store the 1% of the data in a separate column instead?
    – J.D.
    Jun 9, 2022 at 15:44
  • @J.D. thank you for the reply, do you mean that I should separate the columns in this way: column1 VARCHAR (255), column1_long TEXT, column2 VARCHAR (255), column2_long TEXT ?
    – gotali4396
    Jun 9, 2022 at 16:30
  • Yea that seems somewhat logical to me, though I'm not sure if it'll help much with performance other than you can reasonably index the VARCHAR(255) columns then, if needed.
    – J.D.
    Jun 10, 2022 at 11:21

1 Answer 1

1

I expect it will make no difference.

InnoDB stores VARCHAR and TEXT almost identically. If the string on a given row is short, the column is stored on the same page with the other columns of that row. If the string is too long to fit on the page, it is stored on extra page(s) as needed. This applies to both VARCHAR and TEXT (and BLOB).

The differences between VARCHAR and TEXT are therefore:

  • You can declare a DEFAULT for VARCHAR, but not TEXT.
  • You can declare a maximum number of characters for VARCHAR, whereas the maximum length for TEXT is however many characters fit in 64KB.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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