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.
Some Differences Between VARCHAR and TEXT A VARCHAR can be part of an index whereas a TEXT field requires you to specify a prefix length, which can be part of an index. VARCHAR is stored inline with the table (at least for the MyISAM storage engine), making it potentially faster when the size is reasonable.
In most circumstances, VARCHAR provides better performance, it's more flexible, and can be fully indexed. If you need to store longer strings, use MEDIUMTEXT or LONGTEXT, but be aware that very large amounts of data can be stored in columns of these types.
A TEXT column with a maximum length of 16,777,215 (224 − 1) characters. The effective maximum length is less if the value contains multibyte characters. Each MEDIUMTEXT value is stored using a 3-byte length prefix that indicates the number of bytes in the value.
If you use char or varchar, we recommend to: Use char when the sizes of the column data entries are consistent. Use varchar when the sizes of the column data entries vary considerably. Use varchar(max) when the sizes of the column data entries vary considerably, and the string length might exceed 8,000 bytes.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With