mySQL data types - possible db bloat with text type? yes or no?

Normally I use MSSQL but the shop I'm at uses mySQL. I've always built my db's so that the field best matches the data going into it. As an example if I was storing some text data that was max 1000 chars I would use varchar(1000) and not a blob type. I've always thought that this prevented bloating. However I've just been told something that contradicts this and I'm wondering what you experts say. Is using the TEXT datatype completely variable how long it it? So if I insert a single character into a tinytext, text, mediumtext or longtext field it will only take up that much room in the db? Is that correct?

*bump* No one has any insight into this? Please please please. On Thu, Sep 2, 2010 at 11:47 AM, Michael Grant <mgrant@modus.bz> wrote: ----- Excess quoted text cut - see Original Post for more -----

With MSSQL you are limited to about 8000 bytes per row and anything beyond this will be chopped, so you need to be careful how much data you are storing. Using BLOBS will overcome this as a pointer to the blog is the only thing stored in the row, and the blog is actually stored separately. Using datatypes of specific lengths does indeed only take up the space used, but other factors affect thr storage such as page sizes. This paging applies to mysql as well, but I have never looked too deeply into it as I find MySQL very flaky and poor in performance compared to MSSQL so only use it when I have to, such as when I am using PHP apps.. I think this site will help you though., here is a post specifically about page sizes, but check the rest of the site too. http://www.mysqlperformanceblog.com/2006/06/04/innodb-page-size/ You also need to make sure you are using the correct table type (MYISAM or INNODB) -- Russ Michaels www.cfmldeveloper.com - Supporting the CF community since 1999 FREE ColdFusion/Railo hosting for developers. blog: www.michaels.me.uk

----- Excess quoted text cut - see Original Post for more ----- It's not as simple as you think it is.  I don't know the answer for sure but this is what I know. Assuming mySQL...How much space something takes is dependent on the type of engine you select.  The common ones are innodb, myISAM, and the perconaDB. As far I remember the innodb takes more space because it has more features and can't be compressed. Looking at myISAM, which by the way is probably not the engine you want for any transaction based db, you would use one (1) more bytes over a varchar(1000).  So if storing i piece of data as a varchar(1000) that was 299 cahr longs it would take 300 bytes it would take 301 bytes if you had typed is as a text.  That is true up to 65536 bytes.  then the storage requirements changes. recap: in your case if you were using myISAM the text type would use 1 more byte than the varchar(1000).  Things like char encoding like UTF or latin will impact it as well.

Thanks. Other than the 1 extra byte thing... if I'm using MyISAM and assumming I have a string that's 150 characters is there any advantage to using varchar(150) over say TEXT or any of the other text type fields? that's what I'm trying to get at. So is a table that's got 20 TEXT datatype fields going to be the same as a table that has 20 varchar(x) fields?

----- Excess quoted text cut - see Original Post for more ----- There is an advantage in using varchar over text and it is performance. Because  text type is written in a different area of the memory that the row buffer there may be a slight performance hit because it needs to do a harddrive read/write.  I guess like your "does ## impact performance" thread it depends on the data you are storing.  But let me say that you are much more likely to see this performance hit.  I also believe that varchar can be indexed while text can't. I'm a little hazy on remembering everything right now cause I moved to an Oracle shop.

thank you sir. On Fri, Sep 3, 2010 at 12:34 PM, W ----- Excess quoted text cut - see Original Post for more -----