What is more efficient: text (765) or varchar (765) to store a freeform searchable description? EDIT : To paraphrase quesiton - at what point do I switch from varchar to text and back?

A little background on the question. Let's say we have sales_orders table and we want to be able to keep a note field on the order with customer's or sales reps comments. It needs to be searchable LIKE "%goldfinger's gun%" We know we are not going to have "War &amp; Peace" stored in the field, but at same time it should be big enough to handle a sentence or two. 

So which option is:


faster for reads?
better at storing data?
better for full text searching?
indexing?


Assume MySQL(InnoDB) or PostgreSQL use case
As far as Postgres is concerned, there is no difference in performance. And there is no length modifier for text to begin with, just (optionally) for varchar.

Unless you need to enforce a certain length, just use text or varchar.


Would index lookup be noticeably faster with char vs varchar when all values are 36 chars


To support pattern matching with LIKE, I suggest a trigram index. Details:


How to create an index to speed up an aggregate LIKE query on an expression?


There is also full-text-search with its own set of operators, building on dictionaries. Here's an overview of pattern-matching options in Postgres:


Pattern matching with LIKE, SIMILAR TO or regular expressions in PostgreSQL