List:General Discussion« Previous MessageNext Message »
From:Bennett Haselton Date:June 17 2011 8:19am
Subject:Re: optimization strategies based on file-level storage
View as plain text  
At 10:47 PM 6/16/2011, Johan De Meersman wrote:


>----- Original Message -----
> > From: "Bennett Haselton" <bennett@stripped>
> >
> > Do you happen to know the answer to my other problem -- if I have
> > TEXT and BLOB columns but all my other columns are fixed-length, can
> > I still get the benefit of faster lookups resulting from fixed-length
> > rows, if each row just contains a fixed-length reference to the
> > actual TEXT or BLOB data which is stored elsewhere?
>
>entirely unsure, but given the single-datafile structure of MyISAM 
>tables, I strongly suspect BLOBS get stored inline.

I can't find a source that says for sure.
http://dev.mysql.com/doc/refman/5.0/en/storage-requirements.html
does say: "The maximum size of a row in a MyISAM table is 65,535 
bytes. This figure excludes BLOB or TEXT columns, which contribute 
only 9 to 12 bytes toward this size... For BLOB and TEXT data, the 
information is stored internally in a different area of memory than 
the row buffer."  But that's talking about memory, not disk.  When 
people talk about performance improvements from using fixed-length 
rows, are they talking primarily about memory or hard disk?

Hold up though, I just got this reply from posting the question in a forum:
http://forums.mysql.com/read.php?21,423433,423846
which says "Almost always the discussions recommending Fixed length 
records in MyISAM are myths. The apparent performance improvement is 
swamped by the performance loss of shoveling around the wasted 
padding bytes" and goes on to give reasons.

Actually, that does make sense that it's a myth.  I was surprised to 
hear so many sources claiming that there was a big performance 
increase from being able to find row N by jumping to position 
N*rowlength.  Because even with variable-length rows, you can just 
store a table associating row numbers with the position of the row in 
the file, can't you -- which would mean it would only take one 
near-instantaneous lookup to be able to jump to the row you're looking for.

What I was really trying to figure out was why it takes me 4 hours to 
add a new column to my 22-million-row table, and whether a different 
table design can avoid that problem.  That reply in the forum says, 
"ALTER TABLE ... ADD COLUMN will always copy the entire table over, 
and rebuild all the indexes. (And, effectively, do an OPTIMIZE.) Do 
_not_ leave space for extra columns, it won't help."  I'm about to 
reply and point out the trick that you suggested to me: create dummy 
columns early and then just rename them later :)

         -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