By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
449,245 Members | 1,207 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 449,245 IT Pros & Developers. It's quick & easy.

Checking for redundant database tables

P: n/a
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?
Jul 20 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
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.
Jul 20 '05 #2

P: n/a
On Thu, 28 Oct 2004 10:54:39 GMT, in mailing.database.mysql zzapper
<da***@tvisnospam.co.uk> 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?
|

You could use: 'Show table status' to return the creation date/time,
last update date/time.

You'd have to do this for each database.
---------------------------------------------------------------
jn****@yourpantsbigpond.net.au : Remove your pants to reply
---------------------------------------------------------------
Jul 20 '05 #3

P: n/a
Jeff North wrote:
You could use: 'Show table status' to return the creation date/time,
last update date/time.

You'd have to do this for each database.


That's a far simpler solution that what I posted! LOL

But it doesn't find instances where the tables were read, but not updated.

Regards,
Bill K.
Jul 20 '05 #4

P: n/a
On Thu, 28 Oct 2004 11:25:01 -0700, in mailing.database.mysql Bill
Karwin <bi**@karwin.com> wrote:
| Jeff North wrote:
|
| > You could use: 'Show table status' to return the creation date/time,
| > last update date/time.
| >
| > You'd have to do this for each database.
|
| That's a far simpler solution that what I posted! LOL
|
| But it doesn't find instances where the tables were read, but not updated.


True, but I haven't seen/used any databases that contain completely
static data.

The show status function is a good starting place to see what has been
changed and when but like most things, there still needs to be human
intervention to make judgements upon actions.
---------------------------------------------------------------
jn****@yourpantsbigpond.net.au : Remove your pants to reply
---------------------------------------------------------------
Jul 20 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.