MySQL information_schema doesn't update

I have a database, say abc, in mysql server. It has only one table named test. test uses innodb engine and I've set innodb_file_per_table to true.

After I run the query delete from abc.test, I want to calculate the database size of abc. Here is the query I use:

SELECT
    table_schema "name",
    sum( IF(engine = "MyISAM", data_length + index_length -  data_free,
    data_length + index_length)) "size"
FROM information_schema.TABLES
where table_schema like "abc";

The strange thing is that I find the database size doesn't decrease at all, however the data in "test" is gone.

I've done this kind of test many times, this strange behavior happens sometimes.

I'm using percona mysql server 5.5.29-rel29.4.

Can anybody tell me what is wrong?

Update:

Actually, I use another thread to check the database size periodically.

I can tell you why /var/lib/mysql/abc/test1.ibd did not shrink.

The query delete from abc.testl; is a DML transaction. You simply deleted all the row data. If you want to reclaim the physical space of the .ibd file, you need to use DDL instead of DML. I have answered a question of this nature back on Jan 17, 2012 : Problem with InnoDB "per table" file sizes.

Try the following DDL statement:

TRUNCATE TABLE abc.testl;

If the table has no foreign keys, you can do this:

CREATE TABLE abc.test2 LIKE abc.test1;
DROP TABLE abc.test1;
ALTER TABLE abc.test2 RENAME abc.test1;

Here is what you can do right after a full delete of all rows:

DELETE FROM abc.testl;
ALTER TABLE abc.test1 ENGINE=InnoDB;

With regard to checking space, please see my posts with queries that can give you sizes grouped by storage engines and rolled up in summary:

• Sep 13, 2011 : Determining max database and table size supported and present size

• Dec 01, 2011 : MySQL Workbench Database Sizes

• Jan 11, 2013 : Translating backup size to database size

Give it a Try !!!

StackExchange Database Administrators Q#36146, answer score: 2