Re: VARCHAR vs. TEXT - some performance numbers

[Peter Zaitsev]

Thank you for your benchmarks. It is indeed very interesting.

The answer to your first question is partially in the manual partially in your second question. MySQL needs to allocate TEXT column separately which causes some overhead, but I guess it is minimal. The main difference is - "using index" vs not using it - for TEXT case MySQL needs to perform row read, with VARCHAR it only does index read. This is because you do not have full index on TEXT column, so you can't retrieve full row from it in all cases which makes MySQL to use data file. It can't partially use index - even if 99.9% values can be read from index directly it is not enough.

It would be interesting to see results for prefix index in both cases, ie index first 100 characters. I guess difference must be less.

Peter Zaitsev, MySQL Performance Expert
MySQL Performance Blog - http://www.mysqlperformanceblog.com
MySQL Consulting http://www.mysqlperformanceblog.com/mysql-consulting/