Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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.

like image 763
Jonathon Avatar asked May 21 '10 16:05

Jonathon


People also ask

Which is faster VARCHAR or TEXT?

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.

Which is better VARCHAR or TEXT in MySQL?

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.

What is the length of TEXT in MySQL?

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.

When should I use VARCHAR?

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.


1 Answers

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.

like image 192
Quassnoi Avatar answered Nov 15 '22 06:11

Quassnoi