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

3

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.

  • 1
    765 - just wanted a number in a grey zone, not quite big enough for text, not quote small enough that it is obviously varchar. - your comment about 512 addresses that perfectly. To clarify - I'm using utf-8 and InnoDB (technically XtraDB) – konung Mar 6 '15 at 17:48
  • utf8 cutoff is VARCHAR(255) because it assumes up to 3 bytes per character. (utf8mb4: VARCHAR(191)) – Rick James Dec 19 '15 at 1:10
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:

  • Thank you for both links. Do you know if there is difference in mysql or mariadb? – konung Mar 5 '15 at 21:55
  • The trigram index is totally Postgres specific. As is the information concerning data types. There are other regulars here who are more competent with MySQL than me. I'll leave that part to them. – Erwin Brandstetter Mar 5 '15 at 21:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.