MySQL vs PostgreSQL – Text (765) vs Varchar (765) for Freeform Searchable Description

full-text-searchMySQLpostgresqlvarchar

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 & 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

Best Answer

Summary: VARCHAR and TEXT are identical for reading, storing, FULLTEXT, and virtually everything else. Some INDEX/SELECT differences are noted below.

MariaDB and MySQL are (I'm pretty sure) the same as each other in this area.

Some reason for 765? What CHARACTER SET are you using? (Ascii versus utf8 -- there could be differences.)

For "small" VARCHARs (up to 512?), there is a minor advantage over TEXT: When a query need to create a tmp table, small VARCHARs let it be done with MEMORY. TEXT forces MyISAM for the tmp table. This is likely to impact the speed of some queries (MEMORY is faster).

TEXT cannot have an ordinary INDEX; small VARCHARs can. (I am not sure about 765; try CREATE TABLE; it will spit at you if that is too big.)

You can use a "prefix" index, such as INDEX (foo(55)), but it would be useless for your LIKEs, and often useless for any other purpose.

UUIDs should be packed into BINARY(16), similar to what was mentioned for Postgres. 16 is smaller, hence more efficient.

LIKE '%foo%' works the same for VARCHAR and TEXT. Same speed, same syntax, etc. The leading wildcard makes the use of an INDEX impossible, thereby disallowing any index optimization.

FULLTEXT indexes work on VARCHAR and TEXT, in MyISAM and (as of ~5.6.4) in InnoDB. But FULLTEXT is word oriented and has a number of caveats. Furthermore the caveats are different between MyISAM and InnoDB.

Related Question