Hi, I don't think MySQL is doing what's best. *I* am the one who knows best and I don't want MySQL changing column types.
An example is a table I have where I'd like to have a TEXT column with a value that is basically NEVER changed after being inserted. I can run OPTIMIZE on this dynamic row length table if the value of the column is changed to make sure the row isn't split (split rows = slower reads). But I also need the example table to have CHAR(32) and CHAR(96) columns that will be updated VERY often with variable length values. I want *them* to stay fixed length so updating them with longer values doesn't split the row. I DON'T *CARE* that it wastes a few bytes! Of course MySQL won't let me have a TEXT column and those CHARs. :-( So I have to make my would-be TEXT column a CHAR(255), hope 255 chars is enough, and possibly have another column whose value I can concatenate with the CHAR(255) if 255 chars is too short. Really sucks. And no, I won't put the TEXT column in a separate table, because that takes more time for MySQL to check permissions on another table and do the joins than just having dynamic rows (even split ones). At least my fixed length rows can be read faster than any OPTIMIZED/non-split dynamic ones. Oh yeah, also, when I *know* a column will always have n chars, it should stay CHAR(n), not be changed to VARCHAR(n). An example is columns with MD5 values. A CHAR(32) would take 32 bytes; VARCHAR(32) uses 33 bytes. :-( :-( Matt ----- Original Message ----- From: "Brent Baisley" Sent: Thursday, October 30, 2003 2:38 PM Subject: Re: Column Types Changing > There's only one condition that I know of that will cause MySQL to > change char to varchar. If there is another variable length field > defined, like text or blob, then MySQL will change your fixed length > char to variable length char. This is because the existence of a > non-fixed length column makes the record length variable too, so there > is no performance benefit to leaving the fixed length fields fixed, but > there is a storage penalty. > If you think about it, MySQL is doing what is best. > > On Thursday, October 30, 2003, at 03:25 PM, Tom O'Neill ((MySQL User)) > wrote: > > > Someone told me that it is possible that MySQL will automatically > > change > > column types in certain situations. For example the a table with a > > char(5) > > type field might dynamically change to a varchar(5) type field. So a > > static > > length column to a variable length column. Is this possible? If so > > what are > > the circumstances that would cause MySQL to alter the table structure > > on its > > own? This doesn't really make sense to me. > > > > -Tom -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
