MySQL Forums
Forum List  »  MyISAM

how table rows are stored at the file level
Posted by: Bennett Haselton
Date: June 14, 2011 12:30AM

[Tried posting this once before but I don't know what happened to it]

I'm trying to find some documentation how different data types are stored at the file level, so I can figure out how to design the tables so that certain queries should be faster. For example I assume that if every row is of a fixed size, that some row operations will be faster since you can jump to n*rowlength to find row n. Does that mean that all your operations will be faster if you have no variable-length column types, but even *one* variable-length column type will slow you down considerably? OK, then I'm looking for a source that explicitly states that as a tip for optimizing your table speed. Oh, and if BLOB and TEXT data types are stored as a 4-byte reference in each row of the table, does that mean that those data types don't actually make your rows variable-length, so you can include BLOB and TEXT types without sacrificing the benefits of fixed-length rows? If that's the case (or not), then I'm looking for a source that explicitly explains that as well.

Finally, can you design a table so that future table definitions will execute faster, by pre-declaring some extra space in each row to be reserved for future columns? Then you could add new columns to the table, without changing the row length and so without having to move every byte in the file.

I know about
http://dev.mysql.com/doc/refman/5.0/en/storage-requirements.html
but it doesn't really answer the question about optimization. Any tips?

Options: ReplyQuote


Subject
Views
Written By
Posted
how table rows are stored at the file level
4072
June 14, 2011 12:30AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.