CODEWITHSUNDEEP
mysqlpercona
mike_dba
mike_dba

Reputation: 21

question about information_schema.TABLES result

1.we use this sql check big object info of mysql, 

SELECT TABLE_SCHEMA,
       TABLE_NAME,
       CONCAT(ROUND(DATA_LENGTH/1024/1024),'MB') AS DATA_LENGTH,
       CONCAT(ROUND(INDEX_LENGTH/1024/1024),'MB') AS INDEX_LENGTH,
       CONCAT(ROUND(SUM(DATA_LENGTH+INDEX_LENGTH)/1024/1024),'MB') AS TOTDB_SIZE,
       TABLE_ROWS
FROM information_schema.TABLES  
where TABLE_SCHEMA not in ('information_schema','performance_schema','sys')
GROUP BY TABLE_NAME 
order by 6 desc limit 50

the sql result is:

TABLE_SCHEMA TABLE_NAME      DATA_LENGTH  INDEX_LENGTH TOTDB_SIZE    TABLE_ROWS
report       report_opr_detl 1078MB         0MB             1078MB      1778206

it report table report_opr_detl size is 1078MB .the number rows is 1778206 ,

2.but when we check the table size in os filesystem.

it report the size is 1.5G. it is diffrent wih step 1 result (1078MB)

[root@pommodb02 report]# du -sh report_opr_detl*

46K     report_opr_detl.frm
1.5G    report_opr_detl.ibd

it report the count(*) of the table is 2162772 rows. it is diffrent wih step 1 result (1778206 rows)

 mysql> select count(*) from report.report_opr_detl;
+----------+
| count(*) |
+----------+
|  2162772 |
+----------+
1 row in set (0.65 sec)

3.so my question is why step 1 and step 2 is different ,

Upvotes: 1

Views: 83

Answers (1)

Rick James
Rick James

Reputation: 142472

  • The SUM did not include Data_free.

  • TABLE_ROWS is an approximation for InnoDB; the COUNT(*) is exact.

  • Another way to get similar info is SHOW TABLE STATUS.

Upvotes: 1

Related Questions