Rick James Wrote:
> "... convert TEXT columns to VARCHAR and get
> improved behaviour in various parts of the mysql server."
>
> Arjen, can you give me some details, or point me
> at a web page that explains this.
Server-created temporary tables can't be in-memory if you have TEXT or BLOB type columns in your provisional result set. So instead of a MEMORY table a MyISAM table is then used, which is on disk, which is of course slower and less efficient.
MySQL 5.0 supports much larger VARCHAR columns, and thus you can use that to evade the issue somewhat. But just changing them all to varchar is not the solution, as there's more aspects to consider.
Often columns are selected even though they're not actually used (SELECT * being the extreme example), so then changing query or doing 1:1 normalisation can help. Just to give you an angle on what we're talking about.
Regards, Arjen.
--
Arjen Lentz, Exec.Director @ Open Query (http://openquery.com)
Remote expertise & maintenance for MySQL/MariaDB server environments.
Follow us at
http://openquery.com/blog/ &
http://twitter.com/openquery