----- Original Message ----- > From: "Bennett Haselton" <benn...@peacefire.org> > > 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
