MySQL find unused tables

mysql find unused indexes
mysqlindexcheck
mariadb unused indexes
mysql clean index
mysql indexes not used
select from sys schema_unused_indexes
mysql redundant indexes
mysql identify missing indexes

I am working with a database which unfortunately has a lot of unused tables and I am trying to clean it up. I'm trying to find a way where I can be 100% confident that a particular table is no longer being used.

After some googling, I still cannot find a good way to do so. I am only able to tell the last writes to a table (INSERT, UPDATE, etc) using:

SHOW TABLE STATUS or running ls -lt on the mysql datadir (can be found by by running SHOW VARIABLES LIKE 'datadir';)

Do you have any other suggestions?

Thanks.

Try using the INFORMATION_SCHEMA.TABLES. There is a column called UPDATE_TIME. Check the date in that field. If it is NULL, the table has never been updated since the table's creation.

Example: A list of tables not updated in the last 10 days

SELECT table_schema, table_name, create_time, update_time
FROM information_schema.tables
WHERE table_schema NOT IN ('information_schema', 'mysql')
  AND engine IS NOT NULL
  AND ((update_time < (now() - INTERVAL 10 DAY)) OR update_time IS NULL);

Give it a try !!!

Identifying unused database tables, One of the easiest things to do is to drop unused tables. For example, MySQL contains an update_time field inside the write a very simple query - select min( timestamp), max(timestamp) from table - to spot the most recent� To build our query that returns the unused tables, we will retrieve all the database tables first from the sys.all_objects system table, restrict the type of the object to 'U', where the table does not exist in the sys.dm_db_index_usage_stats system DMV; which indicates that no usage has been recorded on these tables since the last SQL Server service restart.

Try that http://forums.mysql.com/read.php?20,298759,299185#msg-299185

Turning on the "general log" and scanning it (programatically!) would tell you who is hitting what tables during the time the log is on. Caution: that file grows fast.

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� Mysql - Find unused tables and columns 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.

I know this is an old question, but there doesn't seem to be a proper answer and I was directed here by my own search for an answer. As per Mark Leith's blog post about unused tables and indexes one should be able to do something like this:

SELECT
    t.*
FROM performance_schema.table_io_waits_summary_by_table t
WHERE
    t.COUNT_STAR = 0
    AND t.OBJECT_SCHEMA = '<your-schema-name-goes-here>'
    AND t.OBJECT_TYPE = 'TABLE';

Official documentation about the topic from MySQL gives more details.

It does of course require that you've enabled Performance Schema and that the statistics haven't been cleared/truncated for some time.

How to find unused indexes in a MySQL database? – EverSQL, Every time we update the table (for example, using an INSERT query), the relevant indexes are updated by MySQL. Otherwise, they will be� In a perfect world an organisation should never get itself into the situation where tables need to be identified as functionally obsolete and removed in bulk. Schema changes should be stepped through the cycle of development, test, staging and live with developers cleaning as they go, but we don't live in a perfect world as…

I am surprised at how little support MySQL has for identifying unused tables. As noted in a comment to this question, at least one promising method turns out to work with MyISAM but not InnoDB tables.

However, I may have found a reliable means of identifying unused InnoDB tables, at least as of MySQL 5.7.26. This method takes advantage of MySQL's file_summary_by_instance table, which aggregates information about I/O operations by filename.

Log in to mysql and:

mysql> SELECT * FROM performance_schema.file_summary_by_instance\G

This prints values such as COUNT_READ for all files in all databases. You can narrow the search in various ways. For example, to show all values for database file /var/lib/mysql/production/user.ibd,

mysql> SELECT * FROM performance_schema.file_summary_by_instance where FILE_NAME = '/var/lib/mysql/production/user.ibd'\G

You can also select only the values you are interested in, e.g.,

mysql> SELECT FILE_NAME,COUNT_STAR,COUNT_READ,COUNT_WRITE,COUNT_MISC FROM performance_schema.file_summary_by_instance where FILE_NAME = '/var/lib/mysql/production/user.ibd'\G

The above queries allow you to monitor usage counts of various types on all files used by your instance of MySQL. However, to tell whether a file is being used at all you probably want to reset these counts. Fortunately,

TRUNCATE TABLE is permitted for file I/O summary tables. It resets the summary columns to zero rather than removing rows.

So you can,

mysql> truncate table file_summary_by_instance;

and then watch which files' counts get incremented.

One caution: because some tables may only be read at application startup, you may want to restart the application (or even the server) and see whether this increases the access counts.

For more details on the file_summary_by_instance table, see https://dev.mysql.com/doc/refman/5.7/en/file-summary-tables.html

MySQL Performance: Stop hoarding. Drop unused MySQL databases, Some times they might be a problem if you find that some tools are causing the tables to be opened by checking every table in� BIG fragmentation! The tables have not been properly maintained, and the fragmentation really is tremendous. We backed up the db and restored it to a dev server so that I could test out my 'solution'. About 3 hours into it, I realized that I probably needed to revisit my approach. At the very minimum, I wanted to exclude the older, unused objects.

MySQL: Hunting down unused databases, How To Find Unused Tables in SQL Server. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana,� MySQL addresses this problem through several statements that provide information about the databases and tables it supports. You have previously seen SHOW DATABASES, which lists the databases managed by the server. To find out which database is currently selected, use the DATABASE() function:

How To Find Unused Tables in SQL Server, mysql> create table index_analysis.used_indexes select * from I can now select my droppable (unused) indexes from this view: Shell. mysql>� For those searching for the inverse of this, i.e. looking for tables that do not contain a certain column name, here is the query SELECT DISTINCT TABLE_NAME FROM information_schema.columns WHERE TABLE_SCHEMA = 'your_db_name' AND TABLE_NAME NOT IN (SELECT DISTINCT TABLE_NAME FROM information_schema.columns WHERE column_name = 'column_name' AND TABLE_SCHEMA = 'your_db_name');

Quickly finding unused indexes (and estimating their size), If you enable it, you will have a table called INDEX_STATISTICS , in the Then we saw how to find unused indexes: the performance_schema� Stack Overflow for Teams is a private, secure spot for you and your coworkers to find and share information. Learn more how find out unused column in a database (SQL Server 2008 ) .

Comments
  • I think the only way to be 100% sure is to resolve all the table dependencies of your application/s. Even if a table hasn't been queried for the last 3 weeks, it could still be occasionally needed by an application, couldn't it?
  • What doesn't SHOW TABLE STATUS tell you that you'd like to know? Granted, if you see that a table has been modified in the last hour you don't know from where, but you do know the table is in use. If it hasn't been modified in a year, that's a good indication it's not in use.
  • @Pekka - I ran a grep on the code base, but there might be scripts running in the wild which still might use the tables.
  • @VoteyDiscip - unfortunately, it does not work on all engines, such as InnoDB.
  • Not for InnoDB see dev.mysql.com/doc/refman/5.5/en/show-table-status.html (show table status uses information_schema.tables)
  • @KCD You are right for InnoDB. If you have innodb_file_per_table enabled, you have to examine the .ibd file's timestamp from the OS.
  • Considering InnoDB is the default storage engine since mysql 5.5, this answer is quite misleading.