CODEWITHSUNDEEP
mysql
AndyZ
AndyZ

Reputation: 615

MySQL - universal way of getting approx. row size in bytes

OK, I won't be mad if nobody wants to help me with this nonsense, but I wanted to have a way to get the approximate row by row byte size of a table in a MySQL console (well I'm using phpMyAdmins SQL window).

The inner query reads the desired table columns from the schema and group_concats them to a to a statement, while adding the char_length() function to every column name. I did not find a better way, than using SEPARATOR for this, but as the separator comes first after the first element, I add two nonsense elements : "empty" at the beginning and "zzempty" at the end. Thoses are stripped of with replace later.

The best part is, I think this code does what I want, but the problem is, the results get fetched but are not displayed.

What is the easiest way to get the results fetched by the prepared statement? Is the only way to get the results to put them into a table?

SET    @query = CONCAT('SELECT ',
 (select
   REPLACE(group_concat( column_name 
 ORDER BY
   CASE 
      WHEN column_name="empty" THEN 0 
      WHEN column_name = 'zzempty'THEN 99 
      ELSE 1 
   END,
   column_name   separator '`) + char_length(`'),
   'empty`) + ',
   '')     
 FROM
   (SELECT
      'empty' as column_name,
      '1' as id 
   UNION
   select
      column_name,
      1 as id     
   from
      information_schema.columns     
   where
      table_schema = 'DB_NAME'        
      and table_name = 'TABLE_NAME' 
   UNION
   SELECT
      'zzempty' as column_name,
      '1' as id ) t1      
group by
   id),' FROM `TABLE_NAME`');   
SET
@query2 = REPLACE( @query, '+ char_length(`zzempty',  '')  ;  
PREPARE stmt FROM @query2;  
Execute stmt ;

In fact, I think there must be much easier way to get the row size, but I did not find any.

Upvotes: 8

Views: 10425

Answers (1)

axiac
axiac

Reputation: 72226

It's easier to ask MySQL about it, using the SHOW TABLE STATUS statement:

mysql> SHOW TABLE STATUS WHERE name='ApiRequest'\G
*************************** 1. row ***************************
           Name: ApiRequest
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 105
 Avg_row_length: 1872
    Data_length: 196608
Max_data_length: 0
   Index_length: 16384
      Data_free: 2520776704
 Auto_increment: 114
    Create_time: 2015-05-29 13:00:13
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_unicode_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

Upvotes: 11

Related Questions