varchar vs text

[Sim Zacks]

Are there any differences between text and varchar? I found the following in
the docs, which leads me to believe that there are no differences at all.
Can someone please confirm this before I switch all my varchars to text?

Tip: There are no performance differences between these three types, apart
from the increased storage size when using the blank-padded type. While
character(n) has performance advantages in some other database systems, it
has no such advantages in PostgreSQL. In most situations text or character
varying should be used instead.

[Martijn van Oosterhout]

On Tue, Jan 04, 2005 at 09:50:18AM +0200, Sim Zacks wrote:

> Are there any differences between text and varchar? I found the followingin
> the docs, which leads me to believe that there are no differences at all.
> Can someone please confirm this before I switch all my varchars to text?

Correct. The only difference between varchar and text is that varchar
can have a maximum length, which will be checked. As for storage used,
indexability, operators, etc they're identical.

Hope this helps,
--
Martijn van Oosterhout <kleptog@svana.org> [url]http://svana.org/kleptog/[/url]

> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see [url]http://www.gnupg.org[/url]

iD8DBQFB3C4XY5Twig3Ge+YRAoyNAKCrU/oBnFNgN65tQ6X9KJ/UrSlwowCeJLyP
NyGjrUAcZM9wO8uH0r85zM0=
=MUxM
-----END PGP SIGNATURE-----

[howachen@gmail.com]

Hi,

Some people said that using Text as datatype is preferred and there is
no reason to use Varchar. Their claimed that that by using Text, no
length checking is needed as varchar, so the performance of using Text
is generally faster.

Anyone did the test before?

Thnaks for any comments.

[Axel Schwenke]

[email]howachen@gmail.com[/email] wrote:

>
> Some people said

Who is "some people"? Some people may be stupid ;-)

> that using Text as datatype is preferred and there is
> no reason to use Varchar. Their claimed that that by using Text, no
> length checking is needed as varchar, so the performance of using Text
> is generally faster.

VARCHAR and TEXT types differ in some points. Read it here:
[url]http://dev.mysql.com/doc/refman/5.0/en/blob.html[/url]

The "length checking" argument is questionable, TEXT columns have
a length restriction too. I also doubt the performance argument
(but never measured this). BLOB and TEXT data is stored separately
from other row data so you have less locality on the storage when
accessing BLOB/TEXT data. So I would expect worse performance.


XL
--
Axel Schwenke, Senior Software Developer, MySQL AB

Online User Manual: [url]http://dev.mysql.com/doc/refman/5.0/en/[/url]
MySQL User Forums: [url]http://forums.mysql.com/[/url]