patternsqlMinor
I have run truncate table but it is still showing table occupying space?
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
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
If I had added an secondary index (not
I might add a hundred rows before the table and/or its index expands beyond 16KB.
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.