HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

I have run truncate table but it is still showing table occupying space?

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
spaceshowinghavebuttruncateoccupyingstilltablerun

Problem

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

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

Solution

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.

Code Snippets

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
...

Context

StackExchange Database Administrators Q#130138, answer score: 4

Revisions (0)

No revisions yet.