Hi All,

We have a table with roughly 263,000 records in it which has a number of indices on it to speed up end-user reporting and looking at migrating into an ndb cluster. I think we have the data size calculations down, but the index calculations left some unanswered questions.

1. Since varchar types are made fixed width in the data calculations, does the same hold true for index memory calculations? E.g. varchar(100) = 101 bytes 2. Do index memory calculations on varchars follow approximately the same formula as MyISAM tables for index size estimates? (key_size+4)/0.67

Here's an example. I realize that there is some sloppiness and redundancy here. I am using this example to learn how some of the calculations are made.

create table leads ( lno int(8) unsigned zerofill NOT NULL auto_increment, dno int(6) DEFAULT 0 NOT NULL, status enum('New','Call','Email') DEFAULT 'New' NOT NULL, lname varchar(100) DEFAULT '' NOT NULL, lemail varchar(50) DEFAULT '' NOT NULL, adate datetime DEFAULT '0000-00-00 00:00:00' NOT NULL, md varchar(60) DEFAULT '' NOT NULL, PRIMARY KEY (lno,lname,dno), KEY status_index (status), KEY adate_index (adate), KEY lname_index (lname), KEY dno_index (dno), KEY dno_status_index (dno,status), KEY lemail_index (lemail(10)), KEY md_index (md(20)) );

Sample calculations (in bytes): Primary key: 4(lno)+101(lname)+4(dno) = 108 + 12(overhead) + 8(over 32 byte primary key) = 129 adate_index: 8(adate) + 12(overhead) = 20 lname_index: 101(lname) + 12(overhead) = 113 dno_index: 4(dno) + 12(overhead) = 16 dno_status_index: 4(dno) + 1(status) + 12(overhead) = 16 lemail_index: 10(lemail(10)) + 12(overhead) = 22 md_index: 20(md(20)) + 12(overhead) = 32

Gives me 129+20+113+16+16+22+32 = 348 bytes per record for indexes Or roughly 91,524,000 bytes index memory for a 263,000 record table. If we follow the MyISAM formula (/.67), and assuming the 4 bytes they added are the same as NDB index overhead, that gives us 136,602,985 bytes.

Did I get close?