calculate size & choosing engine of a mysql Table

Question

this is my table structure in mysql

CREATE TABLE `p_camat` (
  `cid` int(11) NOT NULL AUTO_INCREMENT,
  `pid` int(11) NOT NULL,
  `col1` int(5) NOT NULL,
  `col2` int(5) NOT NULL,
  `col3` int(5) NOT NULL,
  `col4` int(5) NOT NULL,
  `col5` int(5) NOT NULL,
  `col6` int(5) NOT NULL,
  `col7` int(5) NOT NULL,
  `col8` int(5) NOT NULL,
  `row_no` int(11) NOT NULL,
  `col_no` int(11) NOT NULL,
  `mat_dim` int(11) DEFAULT '64',
  PRIMARY KEY (`cid`),
  KEY `pid` (`pid`),
  CONSTRAINT `p_camat_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `p_protein` (`pid`)
) ENGINE=InnoDB AUTO_INCREMENT=2513419 DEFAULT CHARSET=latin1;

As you guys can see there are already 2,513,419 data rows there and there will be like 5-10million more. Also the current db engine is InnoDB.

So how can I calculate the size of this table(like if it has 5M/10M rows)?I read some documents but none of it was definitive.

Also Which engine on mysql would be best(myisam/innodb) as It will have like 85% read & 15% update(no inserts later)?

NB: There are no index there except the fk. I can use no-sql or other database if it serves the purpose.

not really but if the db has ACID props then its better for me. R you going for nosql solutions? — Gun2sh, Jan 10 '14 at 21:50

score 0 · Accepted Answer · edited May 23 '17 at 11:49

0

So how can I calculate the size of this table(like if it has 5M/10M rows)?

Data Type Storage Requirements documents the amount of storage required for each type of value. In your case, all your columns are INT (which occupies 4 bytes per value) and you have 13 of them—so each table record will occupy 13 * 4 = 52 bytes. You can multiply this up accordingly.

Obviously the two indexes will also occupy some space, but the amount of space required will depend upon both the storage engine and the values stored.

You could also query the INFORMATION_SCHEMA for the average row size, which should be 52 bytes since in this case all rows must be that same size:
```
SELECT AVG_ROW_LENGTH
FROM   INFORMATION_SCHEMA.TABLES
WHERE  TABLE_SCHEMA = DATABASE()
   AND TABLE_NAME = 'p_camat'
```
You could even inspect INDEX_LENGTH and, for an approximation, attempt to extrapolate for the larger table size.
Also Which engine on mysql would be best(myisam/innodb) as It will have like 85% read & 15% update(no inserts later)?

It depends upon your requirements: see MyISAM versus InnoDB.

edited May 23 '17 at 11:49

Community

1
1

answered Jan 10 '14 at 21:52

eggyal

122,705
18
212
237

i know the current table size but how can I calculate it for 5/10M rows? – Gun2sh Jan 10 '14 at 21:59
@GunnerAziz: My misunderstanding. See my edit, above. – eggyal Jan 10 '14 at 22:09
thnx,one thing more:can you tell me what will be the size in case of varchar(10)? – Gun2sh Jan 10 '14 at 22:11
@GunnerAziz: As documented under [Data Type Storage Requirements](http://dev.mysql.com/doc/en/storage-requirements.html): "***`L`*** represents the actual length in bytes of a given string value ... ***`L + 1`*** bytes if column values require 0 – 255 bytes, ***`L + 2`*** bytes if values may require more than 255 bytes". – eggyal Jan 10 '14 at 22:19

calculate size & choosing engine of a mysql Table

1 Answers1