CODEWITHSUNDEEP
mysqlsizerowformulamyisam
Bharat Patil
Bharat Patil

Reputation: 1408

Formula to calculate MySql single row size (engine MyISAM)

I have a situation where I have to create tables dynamically. Depending on some criteria I am going to vary the size of the columns of a particular table.
For that purpose I need to calculate the size of one row.

e.g. If I am going to create a following table

CREATE TABLE sample(id int, name varchar(30));

so that formula would give me the size of a single row for the table above considering all overheads for storing a row in a mysql table.

Is possible to do so and Is it feasible to do so?

Upvotes: 0

Views: 2878

Answers (2)

vbence
vbence

Reputation: 20343

It depends on the storage engine you use and the row format chosen for that table, and also your indexes. But it is not a very useful information.

Edit:

I suggest going against normalization only when you know exactly what you're doing. A DBMS is created to deal with large amount of data. You probably don't need to serialize your strctured data into a single field.

Keep in mind that your application layer then has to tokenie (or worse) the serialized field data to get the original meaning back, which has certainly larger overhead than getting the data already in a structured form, from the DB.

The only exeption I can think of is a client-heavy architcture, when moving processing to the client side actually takes burden off the server, and you would serialize our data anyway for the sake of the transfer. - In server-side code (like php) it is not a good practive to save serialized stye data into the DB.

(Though, using php's built in serialization may be a good idea in some cases. Your current project does not seem to benefit from it.)

Upvotes: 2

Devart
Devart

Reputation: 122042

The VARCHAR is a variable-length data type, it has a length property, but the value can be empty; calculation may be not exact. Have a look at 'Avg_row_length' field in information_schema.tables.

Upvotes: 0

Related Questions