MySQL TEXT field performance

Question

I have several TEXT and/or MEDIUMTEXT fields in each of our 1000 MySQL tables. I now know that TEXT fields are written to disk rather than in memory when queried. Is that also true even if that field is not called in the query? For example, if I have a table (tbExam) with 2 fields (id int(11) and comment text) and I run SELECT id FROM tbExam, does MySQL still have to write that to disk before returning results or will it run that query in memory?

I am trying to figure out if I need to reconfigure our actual db tables to switch to varchar(xxxx) or keep the text fields and reconfigure the queries.

Answer 1

I now know that TEXT fields are written to disk rather than in memory when queried

TEXT fields are written to disk only when the query requires a temporary table to store intermediate results of multiple sort or aggregate operations. This, for instance, happens when you mix DISTINCT, ORDER BY and GROUP BY on different columns within a single query.

If your TEXT column is not a part of this temporary table, MySQL will first try to create it using MEMORY engine (which does not support TEXT).

MyISAM engine for a temporary table will only be chosen if the size of this table will exceed @@tmp_table_size or there are some columns MEMORY does not support.

For a query like this:

SELECT  id
FROM    tbExam

, a temporary table will not be needed at all.

There is a slight difference in how InnoDB storage engine plugin (which is responsible for interaction between InnoDB and MySQL) behaves with respect to TEXT and VARCHAR fields: a VARCHAR field is passed to the recordset buffer by value while a TEXT field is passed by reference.

Internally, InnoDB stores TEXT and VARCHAR fields in a same way: in-row if the whole column fits into half of a page, out-of-row if not. The difference above only concerns InnoDB / MySQL interaction issues.

If you don't query for these fields, then there is no difference at all.