A graphical overview of your MySQL database by Christophe Ladroue.
From the post:
If you use MySQL, there’s a default schema called ‘information_schema‘ which contains lots of information about your schemas and tables among other things. Recently I wanted to know whether a table I use for storing the results of a large number experiments was any way near maxing out. To cut a brief story even shorter, the answer was “not even close” and could be found in ‘information_schema.TABLES‘. Not being one to avoid any opportunity to procrastinate, I went on to write a short script to produce a global overview of the entire database.
infomation_schema.TABLES contains the following fields: TABLE_SCHEMA, TABLE_NAME, TABLE_ROWS, AVG_ROW_LENGTH and MAX_DATA_LENGTH (and a few others). We can first have a look at the relative sizes of the schemas with the MySQL query “SELECT TABLE_SCHEMA,SUM(DATA_LENGTH) SCHEMA_LENGTH FROM information_schema.TABLES WHERE TABLE_SCHEMA!='information_schema' GROUP BY TABLE_SCHEMA“.
Christophe includes R code to generate graphics that you will find useful in managing (or just learning about) MySQL databases.
While the parts of the schema Christophe is displaying graphically are obviously subjects, the graphical display pushed me in another direction.
If we can visualize the schema of a MySQL database, then shouldn’t we be able to visualize the database structures a bit closer to the metal?
And if we can visualize those database structures, shouldn’t we be able to represent them and the relationships between them as a graph?
Or perhaps better, can we “view” those structures and relationships “on demand” as a graph?
That is in fact what is happening when we display a table at the command prompt for MySQL. It is a “display” of information, it is not a report of information.
I don’t know enough about the internal structures of MySQL or PostgreSQL to start such a mapping. But ignorance is curable, at least that is what they say. 😉
I have another post today that suggests a different take on conversion methodology.