1

I have run truncate table but the Heidi SQL is still showing table occupying space. Database: MYSQL 5.6.26

enter image description here

I have truncated the con_consumer table but heidi is still showing it taking space. Even other tools like Toad shows it taking up space. Mysql Engine:Inno DB

  • InnoDB or MyISAM? What does Heidi use to compute the space? – Rick James Feb 23 '16 at 17:39
  • @RickJames: Edited answer. Please check – wizneel Feb 23 '16 at 18:42
3

Tables with Engine=InnoDB take a minimum of one block -- 16KB.

mysql> CREATE TABLE minsz (x int) ENGINE=InnoDB;
Query OK, 0 rows affected (0.17 sec)

mysql> SHOW TABLE STATUS LIKE 'minsz'\G
*************************** 1. row ***************************
           Name: minsz
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 0
 Avg_row_length: 0
    Data_length: 16384   <-- Brand new table, still empty, yet 16KB
Max_data_length: 0
   Index_length: 0
...

If I had added an secondary index (not PRIMARY KEY), Index_length would also be one block. (Heidi would probably show 32KiB.)

I might add a hundred rows before the table and/or its index expands beyond 16KB.

  • How does it answer my question? I have run truncate table but it is still showing data in table. Is it some my sql bug? – wizneel Feb 23 '16 at 19:15
  • 2
    It is not showing data in the table. It is showing that a table has some overhead. Even a table with no data takes 16KB. – Rick James Feb 23 '16 at 19:21
  • 1
    Surely you are not worried about the disk space -- it is insignificant compared to the total of 116.1MiB. And, if you want "number of rows", that display does not give you that. And there are less obvious anomalies -- a size could go up when you decrease the number of rows, or vice versa. – Rick James Feb 23 '16 at 19:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.