In the last episode (Oct 27), Baron Schwartz said: > InnoDB has the following extra things, plus some things I might forget: > > a) the primary key B-Tree > b) row versioning information for every row > c) 16k page size; each page might not be completely full > > Those are all counted towards the table size. Actually, the primary > key B-Tree might not be; I'd need to look that up. But I think it > is. Hmmmm. I just tested -- yes, the PK counts towards table size.
In fact, in InnoDB, all indexes count towards table size, since there is a single .ibd file for the whole thing. So you've got the space taken up by your `repid` index to consider as well. Useful reading: http://dev.mysql.com/doc/refman/5.0/en/innodb-physical-record.html http://dev.mysql.com/doc/refman/5.1/en/innodb-physical-structure.html Note the 5-byte header per record in all indexes, plus another 13 bytes per record in the primary key, and the fact that the columns in your primary key are not omitted from the record data. So repid is actually stored three times in the .ibd file; once in the primary key, once in the record, and once in the `repid` index. There's quite a lot of overhead in making a transaction-safe multiversioned table. What I've seen is that for small row lengths (under 50 bytes) an InnoDB table is about twice the size as the same data in MyISAM format (including indexes). For your particular table, you're probably seeing the effect of 16k page sizes. With only 10K rows, your leaf pages are mostly empty. Try putting 100K rows in and see how big the .ibd file is. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
