zzapper wrote:
Hi,
I've inherited a mysql database with many apparently redundant tables (probably abandoned projects).
Without analysing the webpages many of which are also redundant; is there any MYSQL query which can
tell which tables haven't been accessed for say 6 months.
What strategies can I use?
The most sure way is to do a complete code review of your web apps
(including any offline tasks, cron jobs, etc.), and keep a tally of
which tables are touched by the code. But here's a somewhat less
labor-intensive technique:
1. Enable the general query log
http://dev.mysql.com/doc/mysql/en/Query_log.html
Or if your query log has been active, go look at that log file.
2. Leave it going for a few months, until you are fairly confident that
all code in your web apps has executed at least once.
3. Do some analysis or grepping on the query log to find a list of
tables that were touched.
4. Those tables that you believe haven't been accessed, make them
inaccessible. Change the table name, or use privilege management to
restrict access from the user that the web apps connect as.
5. DO NOT LOSE THE DATA. Inevitably something will turn out to be needed.
6. Wait until you get MySQL error messages in the logs, or irate phone
calls, and then start debugging to figure out which of the obsoleted
tables must be restored to accessibility.
7. Repeat as needed for about two years.
Regards,
Bill K.