3

I have the following table:

CREATE TABLE Sdata(uid INTEGER, timestamp DATETIME, value REAL, FOREIGN KEY (uid) REFERENCES Series_uid(uid));

At one point, this table had ~90M rows. I queried for SELECT COUNT(*) FROM Sdata; which took about 7 minutes.

Then I proceeded to DELETE FROM Sdata;. This query took over an hour, which is understandable given the large size. After deleting all rows from Sdata, I ran the COUNT again. This time it still took about 7 minutes.

I'm confused as to why the COUNT still takes a while even though the table is now empty. What is going on here?

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
leonsas
  • 4,718
  • 6
  • 43
  • 70
  • Which storage engine? – Prinzhorn Jun 13 '13 at 08:16
  • All the tables are using InnoDB. – leonsas Jun 13 '13 at 08:18
  • 1
    I'm not 100% on MySQL, hence why this is a comment rather than an answer, however: Try using `TRUNCATE SData` rather than `DELETE FROM SData` - `DELETE FROM` will empty the table but may not entirely clean it up. `TRUNCATE` actually internally drops and rebuilds the table (if you have an auto-increment identity field on it, it will also reset to 1). Also try `OPTIMIZE TABLE SData` – Kai Jun 13 '13 at 08:23

1 Answers1

5

After DELETE FROM Sdata try using OPTIMIZE TABLE Sdata to really discard the unused rows, or in the future, use TRUNCATE Sdata which will do this automatically for you.

Quote from here:

In MyISAM tables, deleted rows are maintained in a linked list and subsequent INSERT operations reuse old row positions. To reclaim unused space and reduce file sizes, use the OPTIMIZE TABLE statement or the myisamchk utility to reorganize tables.

Mladen B.
  • 2,784
  • 2
  • 23
  • 34