MySQL Text vs. Varchar Performance
1–10 of 23 posts
Re: MySQL Text vs. Varchar Performance
#2Can anyone offer a TL;DR?
Re: MySQL Text vs. Varchar Performance
#3Can anyone offer a TL;DR?
if things are slow, check EXPLAIN first.
Re: MySQL Text vs. Varchar Performance
#4Can anyone offer a TL;DR?
MySQL lifts restriction on VARCHAR'length from 255 to 64K. So you can change TEXT columns to VARCHAR(30000). It might not be a good idea to do it though as when MySQL sorts things it uses a temp table with no variable length columns allowed - so these VARCHARs turn to CHARs which can explode the row size and thus the size of the whole temporary table above the maximum size of an in-memory temporary table. This commits it to disk and as it is huge it takes a long time.
Conclusion: long varchars are there but they suck. Limit their length and test test test before deploying changes to the live.
Re: MySQL Text vs. Varchar Performance
#5[deleted]
Re: MySQL Text vs. Varchar Performance
#6Re: MySQL Text vs. Varchar Performance
#7This is why I like VARCHAR(MAX) in SQL Server if the row is 8192 it's TEXT. You only pay the penalty on rows that exceed that threshold. Best of all you can store 2GB of text in a VARCHAR(MAX)
Re: MySQL Text vs. Varchar Performance
#8This is why I like VARCHAR(MAX) in SQL Server if the row is 8192 it's TEXT. You only pay the penalty on rows that exceed that threshold. Best of all you can store 2GB of text in a VARCHAR(MAX) http://msdn.microsoft.com/en-us/library/ms176089.aspx
Using VARCHAR(MAX) still seems to have a significant performance impact, however - http://richardlees.blogspot.com/2010/07/varcharmax-performan... - perhaps the fact it has to check whether the varchar exceeds 8192?
Re: MySQL Text vs. Varchar Performance
#9Earlier quoted context omitted.
if things are slow, check EXPLAIN first.
Which strangely seemed to be one of the last things this guy did, mysql explain and profile are usually enough to see what is going on.
Good advice. "Explain" in this case wouldn't be enough to work out what's going on, however. In both the TEXT and VARCHAR cases he expected the queries to be using temporary tables so the output from mysql explain (both showing the use of temporary tables but not the storage medium such as disk or memory) would not have helped.
Re: MySQL Text vs. Varchar Performance
#10To be clear, it's not just "when a TEXT/BLOB is included in a sort" that causes on-disk temp tables. One would expect to take a hit when sorting on a TEXT field.
But actually it's much worse than that: you can get on-disk temp tables if you're selecting a TEXT field while doing an ORDER BY or GROUP BY that uses any columns from the second table in a join.