mysql large varchar or text - indexing with frequently accessed field

Question

I have a field "descr" varchar(15000) which will regularly hold between 1000 and 5000 characters of text per row, is returned in every query, and will be frequently searched with LIKE %search% (the database is mysql 5.5 and fulltext indexes aren't available). The text inputted is research data and so being unique is not a need - but being searchable is.

The table is innodb with utf-8 encoding. The number of rows is not large (30,000). Maximum index size on the varchar is (255), yet when I do a search on the column, it correctly returns a row with 3000 character input.

I have read a lot on indexing, the most relevant being MySQL: Large VARCHAR vs. TEXT?:

• TEXT is stored off the table with the table .
• VARCHAR is stored inline and is much faster when the size is reasonable and the data is frequently retrieved.

I need help understanding:

 1. What is the performance impact on retrieval (with 30,000 rows)
    going to a text field and     
 2. Is a varchar index workable for
    searching through 3000-5000 character fields? How is the search able
    to find strings with data longer than the 255 varchar index anyway?
    Or would you advise going with mediumtext?

Thanks for your input.

Answer 1

First, it doesn't matter how you index the column if your search uses LIKE %search% because that predicate can't make use of a BTREE index. It's going to do a table-scan regardless of whether you choose VARCHAR or TEXT.

Second, there's no difference between how InnoDB stores VARCHAR vs. TEXT. They are both handled as variable-length strings. If they can fit on the same data page with other columns, then they do. If they're too long for a page (or really a little less than half of one page, because each page must fit at least two rows, plus some header information), then only 768 bytes are stored on the page, and the remainder of the string goes on overflow pages. Unless you declare the table to to use ROW_FORMAT=DYNAMIC, in which case all of the string goes on overflow pages if it doesn't fit on the page. See also http://www.mysqlperformanceblog.com/2010/02/09/blob-storage-in-innodb/

You may also be interested in my presentation Full Text Search Throwdown. I compare other companion technology, such as Sphinx Search.