mysql - store TEXT/BLOB in same table or not?

Question

While searching trough SO, I've found two contradicting answers (and even a comment that stated that) but no definitive answer:

The problem is: is there any performance benefit, if you store a TEXT/BLOB field outside of a table?

We assume:

• You SELECT correctly (only selection the TEXT/BLOB if required, no SELECT *)
• Tables are indexed properly, where it makes sense (so it's not a matter of 'if you index it')
• The database design doesnt really matter. This is a question to identify the MySQL behaviour in this special case, not to solve certain database design problems. Let's assume this Database has only one table (or two, if the TEXT/BLOB gets separated)
• used engine: innoDB (others would be interesting too, if they fetch different results)

---

This post states, that putting the TEXT/BLOB into a separate table, only helps if you're already SELECTing in a wrong way (always SELECTing the TEXT/BLOB even when it's not necessary) - basically stating, that TEXT/BLOB in the same table is basically the better solution (less complexity, no performance hit, etc) since the TEXT/BLOB is stored seprately anyway

The only time that moving TEXT columns into another table will offer any benefit is if there it a tendency to usually select all columns from tables. This is merely introducing a second bad practice to compensate for the first. It should go without saying the two wrongs is not the same as three lefts.

MySQL Table with TEXT column

---

This post however, states that:

When a table has TEXT or BLOB columns, the table can't be stored in memory

Does that mean that it's already enough to have a TEXT/BLOB inside a table, to have a performance hit?

MySQL varchar(2000) vs text?

---

My Question basically is: What's the correct answer?

Does it really matter if you store TEXT/BLOB into a separate table, if you SELECT correctly?

Or does even having a TEXT/BLOB inside a table, create a potential performance hit?

Answer 1

Update: Barracuda is the default InnoDB file format since version 5.7.

If available on your MySQL version, use the InnoDB Barracuda file format using

innodb_file_format=barracuda

in your MySQL configuration and set up your tables using ROW_FORMAT=Dynamic (or Compressed) to actually use it.

This will make InnoDB to store BLOBs, TEXTs and bigger VARCHARs outside the row pages and thus making it a lot more efficient. See this MySQLperformanceblog.com blog article for more information.

As far as I understand it, using the Barracuda format will make storing TEXT/BLOB/VARCHARs in separate tables not valid anymore for performance reasons. However, I think it's always good to keep proper database normalization in mind.