Garbage collection in database? its kind weird idea which I would need suggestions. A classical example:

users
-----
ID | USERNAME
1    test1
2    test2
3    test3

emails
------
USER_ID | EMAIL
1         test1@email.com
2         test2@email.com
2         test3@email.com
1         test4@email.com
3         test5@email.com
3         test6@email.com
2         test7@email.com
3         test8@email.com
1         test9@email.com


and a select:

SELECT * FROM users LEFT JOIN emails ON users.ID = emails.USER_ID


would resuls all emails of users. So far its so good, but lets say its a very big system and we are on MyISAM, so no real foreignkey connectionts. I only delete a user, so all of their emails remain. Normally we would say its bad, because lots of unconnected garbages remain. But what if we wipe out them later? We can SELECT those unnecessary records later and a cron-task would erase them. Is it bad, crazy idea?
MyISAM is especially bad about garbage collection.  That is one of several reasons you really should move to InnoDB.  MyISAM will be removed in the next release.

MyISAM, more so than InnoDB, can sometimes benefit from OPTIMIZE TABLE.  That is the command for freeing up wasted space and otherwise defragmenting a table.  It is tedious and takes a long time (depending on size of table).

As already pointed out, InnoDB, with FOREIGN KEYs, could take care of the deleting.

I would prefer to have an API that handles the delete -- plus any cleanup that might be needed.  Built into that API could be


Cascading delete, or
Application code to do the delete(s), or
A stored procedure to do the delete(s).
A cron job "because it could take a long time to do all the deleted".


A cron job?  -- Suggest you have the app copy the deleted userid into a ToBeDeleted table.  This could be done with a simple TRIGGER.  Then the cron job would select one row from that table, proceed to do deletes from various other tables (COMMITting as it goes), and finally delete from ToBeDeleted.  Rinse and Repeat.

Better yet, don't make it a cron job, make it a continually running job.  (OK, add a "keep-alive" cron job to make sure it does not vanish, especially after a reboot.)  When this continual job finds nothing to do, it sleeps for, say, a minute.  Otherwise, it runs full-tilt.