MySQL Forums :: MyISAM :: how table rows are stored at the file level


Advanced Search

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 3079 Bennett Haselton 06/14/2011 12:30AM
Re: how table rows are stored at the file level 1561 Rick James 06/16/2011 11:44PM
Re: how table rows are stored at the file level 1244 Bennett Haselton 06/17/2011 02:27AM
Re: how table rows are stored at the file level 1527 Rick James 06/17/2011 07:48PM


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.