MySQL TEXT field performance

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.

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.