How to get the amount of space a record occupies in MySQL?

Question:

It turns out that in my database I have a mother table where I enter information from different branches, from which I would need to know how much space all the records of branch 1 occupy on the hard drive and so on with the other branches, in order to get the estimate of how much is the disk price that each branch occupies.

I hope I have made my question a little clear, and that you can help me solve this problem, or if you know of a bibliography on the Internet, it is very helpful!

Thank you!

Answer:

You could solve it like this:

Suppose the database is called mi_db and the table is mi_tabla .

  1. We need to know the total size of the table in question.

     SELECT (data_length + index_length) AS tamano_tabla FROM information_schema.TABLES WHERE table_schema = "mi_db" AND table_name = "mi_tabla";

    This query will return the size in bytes

  2. We also need to know the total number of records in the table .

     SELECT COUNT(1) as registros_tabla FROM mi_db.mi_tabla;
  3. Then we need to know the number of records that belong to a sucursal

     SELECT COUNT(1) as registros_sucursal FROM mi_db.mi_tabla WHERE idSucursal = ID;
  4. Finally, using the rule of three :

     registros_tabla ----> tamano_tabla registros_sucursal ----> X (tamano_sucursal)

    That is:

     X = registros_sucursal * tamano_tabla / registros_tabla

    X will be the size of the branch records in bytes

Scroll to Top