Mysql - Find unused tables and columns

Question

Let's say we have "system A" comprising a MySQL database, with several tables. After a while you want to optimize system A by removing any unused tables and/or columns, system A being quite large and difficult to overlook. Is there a tool or method that lets you run the system for a while, and then do an analysis which prints out general use of tables, columns etc - which would make it possible to find unused tables and columns.

I'm thinking of maybe hooking into the system, logging all SQL commands going to the server, but would in that case rather be doing that on the database side, rather than the application side.

Answer 1

Assuming you are already linking the database with an application of some sort, it shouldn't then be hard to find the columns that are being used over the columns that are not.

Further a program such as profiler (not sure on the MySQL equivalence) can be used to display all the SQL calls. From this you will know all the columns that are being used.

Answer 2

The General Query Log is probably what you're looking for.

According to MySQL, with the General Query Log on:

The server writes information to this log when clients connect or disconnect, and it logs each SQL statement received from clients.

You need to start MySQL with the --log[=file_name] or -l [file_name] option in order to use it.