List:General Discussion« Previous MessageNext Message »
From:Johan De Meersman Date:June 17 2011 5:47am
Subject:Re: optimization strategies based on file-level storage
View as plain text  

----- 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.

For InnoDB, the answer appears to be "it varies": 
  If a row is less than half a page long, all of it is stored locally 
  within the page. If it exceeds half a page, variable-length columns 
  are chosen for external off-page storage until the row fits within 
  half a page. For a column chosen for off-page storage, InnoDB stores 
  the first 768 bytes locally in the row, and the rest externally into 
  overflow pages.

See http://dev.mysql.com/doc/refman/5.0/en/innodb-file-space.html for more on that.

Also, I *think* the concept of "fixed-length" rows is only applicable to MyISAM, InnoDB
has index-organised tables - that is to say, it stores all row data in the leaves of the
primary index. The consequence, of course, is that no additional pointer lookup gets done
for primary key selects; the tradeoff is that all nonprimary key lookups get detoured
through the primary key.


The online documentation is really pretty good; but for the really low-level things, I
guess the best documentation may be the source.


-- 
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel
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