How to find unused columns in MariaDB tables

Question

I have a lot of tables in my DB that have a number of columns which might not be getting used as they slowly lost their relevance with time, I need to find such columns and delete those from the tables, Is there a definitive way to check if a column is getting used or not?

MariaDB 10.3.8

Answer 1

If your table has columns that are defined to have NULL values, run this:

SELECT COUNT(given_column) FROM mytable;

This gives you the numbers of rows in mytable whose given_column is not NULL. If the result comes back as zero(0), that column is not populated with anything. You could drop that column with ALTER TABLE mytable DROP COLUMN given_column;

If your table has columns define as DEFAULT <some-value>, run this

SELECT COUNT(DISTINCT given_column) DistinctCount FROM mytable;

If this comes back as 1, you could most likely drop this column as well.

If the DistinctCount > 1 and you want to see that breakdown, run this

SELECT IFNULL(given_column,'Total Rows') ColumnsValues,COUNT(1) rowcount
FROM mytable GROUP BY given_column WITH ROLLUP;

These queries should give you a good idea which can be dropped.

CAVEAT : Please make sure your given_column is never referenced in your source code before dropping the column

Answer 2

We don't have per-column usage statistics. There are table usage and index usage statistics with the userstat plugin.

The suggestions from @RolandoMySQLDBA cover the values stored in the column, but it requires some assumptions to infer that the values correspond to usage.

I think the only way to be sure about this is either of these two approaches:

• Make a spreadsheet of every table and every column. Do a thorough code review of your client application. Every time you see a table and column referenced, note it as "IN USE" in the spreadsheet. Once you've completed the code review, the columns without a note are candidates for dropping.

• Run a test instance of your application and database. Experimentally drop columns you guess are probably unused. Test every part of your application. If you get no errors, then apparently the column was in fact not needed.