How to quickly analyze MySQL database with ONLY one SQL query

Recently, I had to inspect quite large MySQL database. I wanted quick analysis of the database. First thought was what tool to use for such task? And there are many..

I started with Google search to see what is going on the “MySQL analysis tools scene”, then I thought lets try it with one MySQL select. As the database is on remote server I connected remotely to server, so something quick about the database in one select was always nice. However, the SQL select is bit more complicated if you’re not well aware about MySQL internal tables structures. I tried with usual suspects like stack-overflow but not much is there. I could ask a question and I would probably got the answer, but as a lazy developer I tried GPT bots as a quick solution of something I’m not well versed about. So here is the result which is exactly what I needed for quick overview.

To display the number of rows, columns, the size of each table, and the average records size in a MySQL database, you can modify the query as follows:

SELECT 
    TABLE_NAME,
    TABLE_ROWS,
    COLUMN_COUNT,
    ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024, 2) AS TABLE_SIZE_MB,
    ROUND(((DATA_LENGTH + INDEX_LENGTH) / TABLE_ROWS) / 1024, 2) AS AVG_RECORD_SIZE_KB
FROM (
    SELECT 
        TABLE_NAME,
        TABLE_ROWS,
        COUNT(*) AS COLUMN_COUNT
    FROM
        information_schema.tables
        JOIN information_schema.columns USING (table_schema, table_name)
    WHERE
        TABLE_SCHEMA = 'your_database_name'
    GROUP BY
        TABLE_NAME
) AS table_info
JOIN (
    SELECT 
        TABLE_NAME,
        DATA_LENGTH,
        INDEX_LENGTH
    FROM 
        information_schema.tables
    WHERE 
        TABLE_SCHEMA = 'your_database_name'
) AS table_size USING (TABLE_NAME);

Replace ‘your_database_name’ with the name of your database.

Quite a nice SQL select if you’re working with MySQL databases. I’m sure it’s also easy to come right of the bat for some MySQL guru, which I’m not :). I’ll keep this one in my tool-set. You could probably get nicer and more abundant stats and graphs with some dedicated MySQL analytics, either open source or commercial solution. That’s usually next step in deeper analysis.

It’s also important that the query solution is reasonable fast. For the database of 168 tables and millions of records it took 0.31 sec.

However, AI bots are currently limited to simpler functions but can be of good help if you need relatively simpler solution.