List:General Discussion« Previous MessageNext Message »
From:Bennett Haselton Date:June 14 2011 8:44am
Subject:optimization strategies based on file-level storage
View as plain text  
I'm looking for some tips & tricks documentation that explains how 
different data types in rows are stored at the file level (in MyISAM 
tables, at least), and how to optimize tables for faster queries, 
updates, table definition modification, etc. based on this knowledge.

For example, I've heard that if all of your columns are fixed-length, 
that makes it faster to find individual rows since row N is located 
at position N*rowlength in the file.  (And, presumably, having just 
one variable-length column will slow things down considerably.)  But 
I've also read that having TEXT and BLOB columns will also slow down 
row-finding operations.  This seems to make no sense because I 
thought TEXT was not actually stored in the row, but the row just 
stored a constant-length reference to the TEXT whose actual data was 
stored somewhere else.  Is this correct?  Then is it incorrect to say 
that a TEXT column will slow down the locate-row-N operation, 
assuming all other columns are fixed-length?  This is the kind of 
thing I'm looking for a document to explain.

Another example: It sometimes takes me an extremely long time to add 
new columns to a table.  What if I had a table with all fixed-length 
columns, and I "reserved" some space at the end of each row to be 
used for columns to be added in the future.  Would it then be 
possible to add new columns much more quickly?  You wouldn't have to 
move around the existing row data to make room for the new column 
(although presumably you would still have to *write* to the place in 
reach row where the new column had just been defined, to fill it in 
with its default value).

In particular, I'm not looking for a list of optimization tricks, so 
much as a document that explains how the rows are stored at the file 
level, and thereby explains how the optimization tricks *follow 
logically from* this information.  The reason is that if I just have 
a grab-bag of optimization hints (of which I've found many on the 
Web), some of them will be not applicable to my situation, or just 
plain wrong, and I'll have no way of knowing which ones.  But if you 
know *why* something works, you can more easily figure out if it 
applies to your situation.

	-Bennett

Thread
optimization strategies based on file-level storageBennett Haselton14 Jun
RE: optimization strategies based on file-level storageBennett Haselton14 Jun
  • Re: optimization strategies based on file-level storageJohan De Meersman14 Jun
    • Re: optimization strategies based on file-level storageBennett Haselton15 Jun
      • Re: optimization strategies based on file-level storageJohan De Meersman17 Jun
        • Re: optimization strategies based on file-level storageBennett Haselton17 Jun
          • RE: optimization strategies based on file-level storageJerry Schwartz17 Jun
            • RE: optimization strategies based on file-level storageWm Mussatto17 Jun