Increase TEXT performance?

Question

I need to store text in a MySQL database. The size of the text is different on each row, generally about 10kb, but can be 100kb in some very rare cases.

The text will be accessed pretty frequently for both reads and writes. For this reason I believe it will be best to make it VARCHAR as it's a lot faster than TEXT.

Question: Is it possible to make the column VARCHAR and in the very rare case the text is more than 64kb, somehow store this as TEXT?

Answer 1

The largest VARCHAR you can declare is 64KB. The largest length for a TEXT column is 64KB.

See http://dev.mysql.com/doc/refman/5.6/en/string-type-overview.html

If you need longer strings, you should use MEDIUMTEXT. This data type can store up to 16MB.

Where did you get the idea that VARCHAR is "a lot faster than TEXT?" In the InnoDB storage engine, VARCHAR, TEXT, and BLOB are stored identically.

I read the forum article linked to, and my reactions are:

• The example shown in that thread uses the MyISAM storage engine, which is virtually obsolete and bad practice to use.

• The writer misunderstands what "Using index" means. It really means "Using only index, removing the need to read the table row." When you try to use a prefix index, this spoils any chance of doing an index-only query.

Trying to use a prefix index for a PRIMARY or UNIQUE field has some unintended consequences, like uniqueness cannot be enforced properly:

mysql> create table foo (t text, primary key(t(50)));
mysql> insert into foo values (concat(repeat('a',50), 'x'));
mysql> insert into foo values (concat(repeat('a',50), 'y'));
ERROR 1062 (23000): Duplicate entry
  'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaay' for key 'PRIMARY'

Okay, I think I will use 2 columns, one VARCHAR and one MEDIUMTEXT and have my program use the VARCHAR when possible. This wastes 3 bytes per row and I need to read the VARCHAR all the time to see if empty. Is this a good idea?

I wouldn't do this. It complicates your application when you have to check which column your data is in. Any trivial advantage you might get from storing the strings in one column type or the other are outweighed by the extra coding work you have to do, and the potential bugs you will introduce.

Answer 2

SQL is strongly typed so there is no dynamic type switching. You can use a LONGTEXT column and create an index on the first x-characters of the column. This should give you the query performance you need while allowing you to store large values.

CREATE INDEX ltcol_idx ON my_table (ltcol(64));