Die in a Fire - Eric Florenzano’s BlogWhy the name?

No

You are not correct

http://hackmysql.com/case2

I see what you're saying, but we're really talking about two things. I'm talking about the backend storage definition, and the admin interface is really more display logic. We should choose our database storage based on what fits the problem best, and then make sure that Django's admin interface will display it in a reasonable way.

If you're using MS SQL maybe you should just put a bullet in the server and start over.

I can't speak for MySQL and Postgres as authoritatively, but TEXT columns in Oracle and MSSQL have any number of these limitations:

1. can't compare them
2. can't use LIKE on them
3. can't fetch or insert large ones without using a separate cursor
4. can't ORDER BY them
5. client libraries often have limitations being able to stream them efficiently/easily

Even if MySQL/PG is in fact able to index them, you'll still get poorer performance by indexing a column with potentially huge strings in it versus one which is guaranteed to have strings no larger than a fixed size in them. It's also very reasonable to use size-constrained columns in any case for any type of data that is in fact meant to be a short string, like email addresses. Constraints are basically something you should be using as often as possible in SQL.

I think you're just trying to stir up controversy. :) Performance shouldn't be the only benchmark (I mean benchmark in the philosophical sense). Ease of use in building/maintaining an application never seems to count for as much, but if you're writing core business applications, then that should also be a practical consideration. My point is that I can imagine a lot of cases where the data would become sloppy and unmanageable in a hurry.

Eric, isn't there a way to specify the use of an <input>rather than <textarea> for certain text fields in the admin now? Or am I dreaming…

There is in fact a performance difference in mysql when you are dealing with indexed fields. I have had to optimize the DB structures for sites with hundreds of thousands of registered users with millions of daily hits - one of the biggest performance tweaks was using the correct data type and proper indexing.

If you are recording an email address for example, there is absolutely no reason why you need to use text when the maximum characters for a valid email address is 320

There is a dramatic difference between VARCHAR and TEXT in MySQL (both INNODB and MyISAM). On disk VARCHAR(16) looks like this
(other row data)0x00000000000000000000000000000000(other row data).

On disk, TEXT looks like this
(other row data)0x0383C4BF(other row data)

Where 0x0383C4BF is a made up pointer to a text heap stored alongside the table.

The implications are:
TEXT costs are O(len(text)), while VARCHAR is O(1) (your predefined size)
Reading a VARCHAR is fast, on InnoDB it's interleaved with the primary keys index, and is probably in cache.
Reading a TEXT can be slow, it's a random read into the TEXT heap, which could suck.
Reading a TEXT can be fast, if the data is rarely there but often accessed then the TEXT heap is small, and probably in cache.

On small datasets (less than your memory) or where throughput isn't an issue (small website), the perf differences come out a wash. Either way, do whichever is easier first and worry about perf later!

@ Everybody:

This is my conclusion: It doesn't matter if you pick a VARCHAR(4) or a VARCHAR(255), as the resulting used space will be the same. So I always use VARCHAR(255) wherever I can choose an arbritrary size > 4. Maybe not if it's clear that there can only be a specific length, as not to confuse my coworkers, but if its a name or an email-address or something similar, I go with VARCHAR(255).

For very short string columns (< 3 in length, I think) it's more efficient to chose a CHAR, because you won't need the length byte of the VARCHAR.

I still won't pick TEXT - Fields for anything I'll have to sort, group, order by or may be forced to use functions on someday, even though maybe I need to do some reading on indexes for text fields. But I generally feel more flexible if I can run functions on the fields inside of the table directly than if the database has to go and search for the data by the pointer in the TEXT column.

All dbs mentioned, MySQL, Postgresql and MSSQL, have a limit of 8kb rowsize. If you sum up how much data your row can handle, without indirection pointers (blob/text), then the limit is 8kb.

Try :
create table test ( a varchar(8000) , b varchar(8000) ) ;

You will get a warning when creating this table, and an error when len(a)+len(b)>8000.

Even more tricky with joins:

create table test_a ( id_a int, a varchar(8000) ) ;
create table test_b ( id_b int , b varchar(8000) ) ;

All ok. Fill up tables, now do:
select * from test_a inner join test_b on id_a = id_b;

Error because:
len(test_a.a)+len(test_b.b)>8000

Use text if you want to avoid rowsize issues.

And on another note, I see people comment on indexing of varchars vs text. Varchar has stupid indexes, Text columns have full text indexing.

Similarily, ordering on text is nicer if you sort by full text search weight.

ps: Ironicly, this comment can only be 1000chars

why does one declaring a size make the actual size that you'd optimize for?

Why not just let the datbase figure out lengths from the actual data and use that for optimizations?

A couple things: 1) use varchar for name type attributes. 2) use text for possibly long, unknown length input; you should protect against an unreasonably long length text insertion either with an insert check constraint, or at your application level.

By default, in Postgres, varchar is unlimited length, max row size is 1.6 TB, and max field size is 1 GB. With that said, refrain from using varchar for non label type fields. There are internal implicit type casts that take place in the database, and you should use the proper field types instead of attempting catch-alls. You might want to index a varchar, but you'll never want to index a text field. You should use a text search on a text field, like TSearch2.

This blog post is horrible. Please consider adding an "edit" to state you were completely misinformed.

One benefit to using varchar over text in MySQL is that if you set your varchar field to be binary, it's values are case-sensitive, which is helpful for password checking. Also, if your passwords are encrypted and are always the same length, a character limit is a good idea to avoid program errors.

In contrast to CHAR, VARCHAR values are stored as a one-byte or two-byte length prefix plus data. The length prefix indicates the number of bytes in the value. A column uses one length byte if values require no more than 255 bytes, two length bytes if values may require more than 255 bytes.

The length prefix indicates the number of bytes in the value.

I need to do some reading on indexes for text fields.

Or maybe even an algorithms class to learn why knowing the size can change how you search/order/sort things.

I need to do some reading on indexes for text fields.

Or maybe even an algorithms class to learn why knowing the size can change how you search/order/sort things.

The length prefix indicates the number of bytes in the value.