COUNT(*) takes long even after deleting all rows

Question

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?

Answer 1

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.