VARCHAR too small?

Anon

mySQL has a 255 VARCHAR limit, I have been working on other bigger DBases and they have a much bigger limit (ie.informix->32,000), I have been reading that using TEXT or BLOB fields is not good for search queries.

Pepe
Zaragoza 2001

[deleted]

Informix's varchar is max 255 too isn't it?

Whats bad about using text is also bad about varchar: they have a variable size.
That means that the database does not only store the content of the column, it also has to store the size of the content, which slows it down.

Anon

Informix does not do badly. They let you store 32,000 characters in a VARCHAR and insert those 32,000 characters in a string literal. The Netscape browser won't let users put much more than that in a TEXTAREA so it is nearly a perfect fit with the Web.

As noted above, Oracle8 lets you have 4,000 characters in a VARCHAR. It turns out that this is enough for 99 percent of Web interactions but not for that last 1 percent. photo.net is Oracle-backed and this limitation has been one of the most painful for me (see http://photo.net/wtr/oracle-tips.html).

A surprising number of RDBMSes have the ridiculously low limit of 255 characters beyond which you're forced to use special SQL-unfriendly data types.

Caveat emptor.

Writen by Philip Greenspun at:

http://www.arsdigita.com/books/panda/databases-choosing

Regards Pepe

[deleted]

I wonder where this guy Greenspun got his knowledge from. :-)

The reason why most database have a very low limit for varchar is that varchar is stored in the records themselves.
If you put 125MB of data in a varchar(if that were possible) all 125MB would end up inside the record, thus making that record 125MB in size. That does NOT help for speed.

Oracle and Informix solved this by making the limit for varchar much lower, and forcing you to use TEXT/BLOB types. As soon as you store data in a TEXT or BLOB type in Oracle or informix, the data is physically stored in a seperate table, and transparently linked to the original record.

Most databases can index TEXT types, so why this is an 'SQL unfriendly' datatype is beyond me.

Anon

Thank you Vincent, I was also concerned about this comment, that´s why I posted it...by the way this 'guy' is a web design teacher at MIT and develops a few 500 Fortune websites, his photo.net site has 20 hits per second.

Regards,

Pepe

[deleted]

A web design teacher who needs to refresh his database knowledge :-)

How popular a site is says nothing about how clever it's database design is.
And to add to that, 20hits/sec is about one pageview/sec, which is not a lot compared to some sites I've hosted. One site drew 277 hits/sec, that's well over one million hits per hour and it was powered by.... mysql, including those 'bad' TEXT columns.

Just goes to show, MIT or no MIT, nobody knows everything!
...well, except me ofcourse.. but I'm a bot...
I am vincentius of borg, if ($resistance==FUTILE) { unset($world) };

Anon

I liked his book, you could take a look and some chapters and tell me what you think, he is a little snob but some thoughts are very interesting...

http://www.arsdigita.com/books/panda/databases-interfacing

Regards

Pepe

Anon

gentlemen, gentlemen,

calm down.

vinne mate sit back hav' a pint and chill.

pepé u spanish?

you might one day discover MySQL is very good and vinne knows what he's talking about. see you can ask him to program life in php and he'll work it out plus he's dam helpful. Anyone want to join the vinne fanclub?

[deleted]

hehe thanks Ashley, but we weren't fighting, just discussing some things, nothing hostile going on.

A good exchange of opinions helps to keep your mind open to new things.

[deleted]

That document is quite old, and I'm sure he has gathered more experience over the years.

I could have a field day correcting him, expecially as on this page he is talking about security and starts by saying he's no expert on it. Well then sh*t up! :-)

He is worried about storing password in plaintext for CGI scripts, because if someone gets root permissions on your server, they can read the CGI scripts.
Hellooo! if they can get your server's root permissions, you have a lot more to worry about than a few passwords to some database. giggle
Putting everything behind a firewall because databases like Informix don't have proper security? giggle did he ever work with informix at all? Or unix for that matter?

Oh well, enough of this, he's not here to defend himself, so I'll just conclude that at the time he wrote this document he clearly was 'not an expert' and he probably shouldn't have written it.

Anon

ok Vincent, nice points, this book was re-edited this year or so. There are a few nice points I never thought about.

How about that AOLserver and Tcl pages he is using, are they any good?

Regards

Pepe
Zaragoza

[deleted]

AOLserver? giggle a java-based client? !SHUDDER!
Even the author warns agains using that.

The whole point of this article is that you should not let outsiders connect directly to your database. What does a java applet do? Yup, plug straight into the database. ieuuw! 🙂

Anon

Yes, It seems as I this guy is getting some bucks/sponsorship from Oracle and AOLserver, he also talks nicely about ASP, but not well about Microsoft team.