Physically deleting tables rather than dropping

[Frank_Rizzo]

I was going to write some code to check for tables which hadn't been access for x months, and then drop them. But I though of a quicker (lazier) way.

Is it possible to just rm files in the data directory?

i.e. in the mysql databases directory, sort by last access date and delete any file which has not been accessed for x months.

Could use find to locate and delete the files. Could also setup a cron job to automatically delete.

Is physically deleting tables and their indexes really a bad idea? Are there pointers to the files like in a master mysql index or something?

[lorax]

That's a very good question - wish I had the answer. My guess is that there are relationships established for each of the tables that define the table type, num of fields, etc... and that these are stored as well.

[Frank_Rizzo]

The only problem I can think of is on the offchance that someone accesses a table whilst it's being deleted.

I'm not too bothered about that as it's probably highly unlikely that this would happen.

What I am concerned about is if there is any kind of master file / index which keeps tabs on all the tables.

e.g. when you do a show databases or show tables, does mysql just physically read what is in the data dirs or does it look up in an index?

[killroy]

Actually, that's part of the beuty of MYSQL. Make a folder, got a database, rename a file, renamed a table. Just might have to flush to make sure hte table is closed.

SN