MySQL Question

what comes between varchar(255) and text

[acidic]

I have recently discovered that the database for my site takes up over 1GB of space. I think the main reason for this is that I am using text fields way to often. Is there any field that comes between varchar(255) and text. Something that offers say 1024 characters or so.

Thanks

[roitracker]

<edit>Had a blonde moment</edit>

[edited by: roitracker at 11:30 pm (utc) on Oct. 3, 2003]

[acidic]

Doesn't TINYTEXT also only offer 255 characters? Or am I mastaken?

[jatar_k]

they are all listed here, see what there is
MySQL Column Types [mysql.com]

[ergophobe]

What you actually want is this page

[mysql.com ]

If you look at that, you will see that TEXT and VARCHAR fields are actually pretty efficient.

A TEXT field with 2 characters in it will only require 6 bytes (2 for the characters, 4 for fixed overhead on a TEXT field).

In a VARCHAR(255), they would take up three bytes (2 plus 1 of fixed overhead)

By contrast, if you had a CHAR(255), those two charcters would take up 255 bytes (fixed length, but no overhead).

So let's take the worst case situation -

1. you should be using a CHAR(1) field, taking up 1 byte per record.
2. but you are using a TEXT field, storing one character and taking up 5 bytes per record (that's the worst ratio you can achieve).

To end up with an extra 800MB bringing you from an expected 200MB to 1GB, you would need 200,000,000 records. Assuming you had two hundred such fields, you would still need a million records.

I would guess it has to be something else

[jatar_k]

good call ergophobe, nice post, thanks