Hi Brent, ----- Original Message ----- From: "Brent Baisley" Sent: Friday, October 31, 2003 7:39 AM Subject: Re: Column Types Changing
> Unless your TEXT field has the exact same length in every record, it > doesn't really matter whether it changes or not. You have variable > length records. Thus, the database can't guess where the nth record > will start or end like it can with fixed length records. MySQL changes > the char to varchar to make the records smaller and thus the database > file smaller resulting in faster searches. Disk fragmentation not > considered. Even if every row was the exact same length in a dynamic table, it wouldn't be able to "guess" where rows start any more than if there was a huge variance in length. Dynamic is dynamic, even if it's really "not." :-) Only with fixed records does it *know* where the row starts. And again, I don't want the records and table smaller. I just don't want the rows to be split. The rows would never be split if I could keep the CHAR columns and run OPTIMIZE after the TEXT column is updated, if it is. But with VARCHAR, whenever the column is given a longer value than it had since being OPTIMIZEd, a link will be added to point to the part of the data file where the row continues. :-( I had thought that I could right-pad the VARCHAR columns with spaces to their max length, OPTIMIZE, and then TRIM them. This would ensure that the rows never get split. But then, I remembered that MySQL won't allow that since it strips spaces from the end of VARCHAR columns, which shouldn't be done. :-( And no, I can't use TINYTEXT because of the way I need the columns indexed. Oh and I don't think *disk* fragmentation is an issue because the table is always scanned and small enough that it will be cached by the OS. Thus there should never be any physical disk reads after the first acesses. > I understand your arguments, but I'd be curious really how much > performance difference there is with splitting your text field into a > separate table. I've never tested nor read about how much overhead > there is in permissions checking. MySQL does cache that information so > it can't be much. Yes, it's not very much to check permissions. Only a fraction of a millisecond, but I care about every little bit. :-D But having yet another table to join is another table to use space in the table_cache (heh), plus the overhead of actually doing the join. Doing the join would take longer than looking up links in a split dynamic table. I've tested it. :-) > I think your best bet would be to setup multiple char(255) columns > instead of text. There is extra coding required when adding records, > but it gives you your fixed length data since the fields are padded by > the database. Like I said, I think I'm going to sorta do that. I have another field that will usually be empty and if it is I can CONCAT it with the CHAR(255) column if needed. If this other field isn't empty, then 255 chars will have to be the limit. :-) > But here is another idea. Since your text value is not changing, what > about setting it up in a separate table and loading it into a HEAP > table at startup? Adding records would require updating both the HEAP > table and the "real" table, but retrieval would be fast. One problem: HEAP tables don't currently support TEXT/BLOB columns. ;-) That's OK though, because you'd still have to do the join. And in my experience, reads from HEAP tables aren't really any faster than a MyISAM table that's cached by the OS. They're fast for writes though! > Just some ideas that may or may not work for you. I don't claim to know > what's best for you, I'm just trying to help. Yeah, thanks for trying to help! Matt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
