2

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

konung
  • 145
  • 1
  • 7

2 Answers2

5

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.

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

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:

Erwin Brandstetter
  • 185,527
  • 28
  • 463
  • 633
5

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.

Rick James
  • 80,479
  • 5
  • 52
  • 119