List:General Discussion« Previous MessageNext Message »
From:Bennett Haselton Date:June 14 2011 2:29pm
Subject:RE: optimization strategies based on file-level storage
View as plain text  
At 05:46 AM 6/14/2011, Carlos Eduardo Caldi wrote:
>Hello Bennett
>
>
>On the Mysql developer site have a grate documentation, try the 
>links above.
>
>http://dev.mysql.com/doc/refman/5.0/en/optimizing-database-structure.html
>http://dev.mysql.com/doc/refman/5.0/en/data-size.html

Thanks, this gets me a little closer to the answer but doesn't really 
provide the level of detail that I'm looking for.  For example, it 
says: "For MyISAM tables, if you do not have any variable-length 
columns (VARCHAR, TEXT, or BLOB columns), a fixed-size row format is 
used. This is faster..."  I still don't understand: If TEXT and BLOB 
columns are stored not by putting the data in the row but by having 
the row store a reference to the TEXT/BLOB data stored somewhere 
else, then can't a row with TEXT and BLOB data types *still* be a 
fixed-size row, with the resulting increased speed?

My main motivation is that I have a table with 22 million records and 
it takes a few hours for me to add a new column to it.  I suspect 
this would be faster if I designed the table correctly from the 
beginning, and I want to change it to a smarter design, but I only 
want to do it once.  So I want to understand really thoroughly *why* 
a different design would make it faster to complete the table 
modifications.  (For example, the question I asked earlier about 
whether you can declare extra space at the end of each row that is 
"reserved for future columns".)


>Att.
>Carlos,
>
> > Date: Tue, 14 Jun 2011 01:44:47 -0700
> > To: mysql@stripped
> > From: bennett@stripped
> > Subject: optimization strategies based on file-level storage
> >
> > 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
> >
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe: http://lists.mysql.com/mysql?unsub=1
> >

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