How to know what is the memory consumption of an single record in database table mysql

Question

I am having a DB table with only 35000 of records on export of DB I found that the file size is more than 2 GB .

Even my table schema does not contains any BLOB type of data. Is there any way to identify Table row size in MySQL.

Possible duplicate of [How to get the sizes of the tables of a mysql database?](http://stackoverflow.com/questions/9620198/how-to-get-the-sizes-of-the-tables-of-a-mysql-database) — AndreFeijo, Mar 01 '17 at 05:01

score 1 · Answer 1 · edited Jun 20 '20 at 09:12

What if we first find out the size of the consumed memory by an empty table with performing the next query:

SELECT table_name AS "Table",
ROUND(((data_length + index_length) / 1024 / 1024), 2) AS "Size (MB)"
FROM information_schema.TABLES
WHERE table_schema = "database_name"
AND table_name =  "your_table_name";

Then insert in your table several rows of data, then perform the above query again and calculate the difference and the average value of memory consumption for one record. Example: I have a table:

create table returning_players
(
casino_id               int not null,
game_id                 int not null,
returning_players_count int null,
primary key (casino_id, game_id)
);

so empty table consumes: then insert into table 1000 000 records:

DROP PROCEDURE IF EXISTS query;
CREATE procedure query()
BEGIN
    declare counter int(20) default 0;
    LABEL:
    LOOP
        IF counter >= 1000000 THEN
            LEAVE LABEL;
        END IF;
        INSERT INTO returning_players VALUES(counter, 45, 5475);
        set counter = counter + 1;
    END LOOP LABEL;
END;
CALL query();

and now we have memory consumption: as you see that we have approximately consumed 32.56 MB per 1000_000 rows. From where we can get the consumption by one row.

How to know what is the memory consumption of an single record in database table mysql

1 Answers1