{question}
How to estimate rowstore table size when moving from MySQL to SingleStore?
{question}
{answer}
There is a general formula for estimating rowstore table size:
(data size + 64 bytes (primary key + versioning) + 40 bytes * number of secondary indexes) * number of rows
- data size: the size of the data type. A list of different data types and their sizes can be viewed here.
- primary key: 40 bytes.
- versioning: Has a variable size up to 24 bytes, so please consider 24 bytes in the calculation
- secondary indexes: each one takes 40 bytes. If there are no secondary indexes, then set it to 0.
- number of rows: total or expected number of rows for the table
For example, a Table has the following DDL:
CREATE ROWSTORE TABLE transaction( id BIGINT NOT NULL, explanation VARCHAR(70) SPARSE, shares DOUBLE, share_code CHAR(4), transaction_date DATE, PRIMARY KEY(id), KEY code(share_code) );
To calculate the data size:
BIGINT + VARCHAR(70) + DECIMAL + CHAR(4) + DATE
BIGINT: 12 bytes
VARCHAR(70): 72 bytes (nearest roundup number in multiple of 8's)
DOUBLE: 8 bytes
CHAR(4): 17 bytes (4*LENGTH + 1 bytes)
DATE: 8 bytes
Then we have the primary key + versioning (40 bytes + 24 bytes for the versioning) and the secondary index (40 bytes).
If we assume we have 2.000.000 rows, the full formula becomes:
Sum of data types: 12+72+8+17+8 = 117
primary key + versioning: 64
secondary indexes: 40
Bytes per Row: 117 + 64 + 40 = 221
221 bytes x 2.000.000 rows = 442,000,000 bytes
More information can be found in our Rowstore Sizing Estimations documentation.
{answer}