Re: when to use char, varchar or text

> I'm making a table to hold a queue of incoming and outgoing email
> messages. I'm confused about using char, varchar or text fields - I
> imagine they are searchable (without and index) with decreasing speed
> from char to text. Is that right?
>
> The (very simplified) column setup I have includes:
>     to            varchar(200)
>     from          varchar(200)
>     cc              varchar(200)
>     subject          text
>     body          text

I have similar tables on my system, they store literally millions of
records.  Do you know how long it takes to search this table?  Forget it.

You'll find searching a lot easier with something like this:

Email
-----
id SERIAL PRIMARY KEY
body text not null
subject text not null
sent datetime
received datetime
priority int2 not null
errorcode int2 not null
index(sent)
index(received)
index(priority)

Email_Header
------------
id SERIAL PRIMARY KEY
emailid int4 references Email.id
name varchar(64) not null (Probably needs to be larger)
data varchar(200) not null
index(emailid)
index(name)
index(data)

This way, you associate email headers with the message, multiple "To"s can
be subverted into multiple header records.  It is a lot easier to search
this kind of structure, IMHO.  You can also search on sent / received to
determine which type of email it is, outgoing or incoming.  Priority can be
used to get more important outgoing emails out first.  Errorcode may be
useful if you're storing the SMTP results of an emailing.  (ie. If you want
to remember why an email failed).

Adam