mysql table very large size although it doesn't have a lot of records I have drupal website, one of the tables is cache_form.  I have a cron to  

DELETE FROM cache_form where expire &lt; UNIX_TIMESTAMP(NOW());


which works fine.  

The size of cache_form.myd is 4.9G although I have this:

mysql&gt;select sum(length(data))/1024/1204 from cache_form;

11.19144630 &lt;b&gt;==&gt; 11 M

mysql&gt;check table cache_form\G
Table: drupal.cache_form
Op: check
Msg_type: status
Msg_text: OK

mysql&gt;select cid,length(data) from cache_form order by length(data) desc limit 1;
82373 ==&gt; 80K

select * from information_schema.tables where table_name='cache_form'\G
*************************** 1. row ***************************
  TABLE_CATALOG: NULL
   TABLE_SCHEMA: drupal
     TABLE_NAME: cache_form
     TABLE_TYPE: BASE TABLE
         ENGINE: MyISAM
        VERSION: 10
     ROW_FORMAT: Dynamic
     TABLE_ROWS: 2681
 AVG_ROW_LENGTH: 4621
    DATA_LENGTH: 12391088 &lt;b&gt;==&gt;11M&lt;/b&gt;
MAX_DATA_LENGTH: 281474976710655
   INDEX_LENGTH: 177152
      DATA_FREE: 0
 AUTO_INCREMENT: NULL
    CREATE_TIME: 2011-07-11 19:07:38
    UPDATE_TIME: 2011-12-26 08:40:09
     CHECK_TIME: 2011-12-26 08:09:45
TABLE_COLLATION: utf8_general_ci
       CHECKSUM: NULL
 CREATE_OPTIONS: 
  TABLE_COMMENT: 


I have tried to repair it

mysql&gt; repair table cache_form;
+-------------------+--------+----------+----------+
| Table             | Op     | Msg_type | Msg_text |
+-------------------+--------+----------+----------+
| drupal.cache_form | repair | status   | OK       |
+-------------------+--------+----------+----------+


then I got the correct size!!

Any idea of what might cause cache_form.myd file to be with this size and repair table finally removed the extra space?

Thanks
When it comes to a MyISAM table, deleting rows does trigger a kind of garbage collection in that all the unused space is recording in a linked list.

The data length you saw is correct. Your cron job deleted every row less than UNIX_TIMESTAMP(NOW()). This will not cause the table to shrink. All the delete rows are just chained together.

THere are other options you could have chosen to shrink the table:


Option 1 : ALTER TABLE cache_form ENGINE=MyISAM;
Option 2 : OPTIMIZE TABLE cache_form;
Option 3 : Manually copy to a temp table


Option 1 simply does the following


copies data pages in the .MYD to a temporary .MYD and renames it back to cache_form.MYD.
copies index pages in the .MYI to a temporary .MYI and renames it back to cache_form.MYI.


Naturally, Option 1 will not copy the unused space.

Option 2 performs the same operation as Option 1 and then performs ANALYZE TABLE cache_form; to compute index statistics.

Option 3 would simply be an emulation of Option 1. Here it is:

CREATE TABLE cache_form_new LIKE cache_form;
INSERT INTO cache_form_new SELECT * FROM cache_form;
ALTER TABLE cache_form RENAME cache_form_old;
ALTER TABLE cache_form_new RENAME cache_form;
DROP TABLE cache_form_old;


Whenever you did the compression this way, find out what data_length is for cache_form_new. This will give you an idea what the actual size should be.