Information Schema en MySQL: Análisis Interno de tu Base de Datos

Database management goes beyond simple SQL queries. Knowing the internal tools that MySQL offers us to monitor and understand our own databases is fundamental for any developer or system administrator. This time, we will explore one of the most valuable but less known databases: Information Schema, which allows us to obtain detailed information about our tables, indexes and general performance.

What is Information Schema and why is it so valuable?

Information Schema is a special database that MySQL automatically provides to all users, even those with limited permissions. This database contains tables with metadata about all other databases on the server, their tables, columns, indexes and much more.

Unlike the MySQL database (which stores system configurations), Information Schema focuses on storing hard data about our data structures. This information is extremely valuable for monitoring the growth of our databases, optimizing queries and better understanding our data model.

To access it we simply use:

USE information_schema;SHOW TABLES;

Exploring the TABLES table

Among all the tables available in Information Schema, one of the most useful is TABLES. This table contains detailed information about all tables in all databases on the server:

SELECT *FROM information_schema.TABLESWHERE TABLE_SCHEMA = 'platzi_sql';

This query shows us information such as:

• Table name
• Table type
• Storage engine
• Row format
• Number of rows
• Average size of each row
• Total data size
• Index size
• Creation date
• And much more

Creating a view to monitor the state of our database

We can create a custom view that shows us the most relevant information about our tables, including byte conversions to more readable units:

CREATE VIEW db_status ASSELECT TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE, TABLE_ROWS, DATA_LENGTH / (1024 * 1024) AS DATA_LENGTH_IN_MEGABYTES,
    AVERAGE_ROW_LENGTH / 1024 AS AVERAGE_ROW_LENGTH_IN_KILOBYTES, INDEX_LENGTH / (1024 * 1024) AS INDEX_LENGTH_IN_MEGABYTESFROM information_schema.TABLESWHERE TABLE_SCHEMA = 'platzi_sql';

This view allows us to quickly query the status of our database:

SELECT * FROM db_status;

How to interpret the information about indexes and table size?

By analyzing the results of our db_status view, we can obtain valuable insights about our database:

1. Relationship between data and indexes: In the example shown, the clients table has a data size of about 20 MB, while its indexes occupy about 5 MB. This means that the indexes represent approximately 25% of the total size.

2. Table growth: Monitoring the growth of tables over time helps us identify whether our catalog tables are behaving as we expect or if they are growing faster than expected.

3. Data volume: Seeing that we have 96,000 customers in a week of operation can be an important indicator of business performance.

Operational benefits of monitoring these metrics

Creating a view like db_status has significant operational benefits:

• It provides a "real-time speedometer" of your database status.
• Helps you understand which tables are growing the fastest
• Allows you to identify if tables are behaving as expected (catalogs vs. operational tables)
• Facilitates optimization and maintenance decisions

What other useful tables exist in Information Schema?

Although the TABLES table is extremely useful, Information Schema contains many other tables with valuable information:

• COLUMNS: Information about all columns in all tables.
• STATISTICS: Data about indexes
• KEY_COLUMN_USAGE: Information about key restrictions
• INNODB_METRICS: InnoDB engine specific metrics
• And many more

While these additional tables may not be necessary on a day-to-day basis, knowing their existence is valuable for when you need to troubleshoot issues specific to your installation, model or business.

MySQL's transparency in providing access to its internal statistics is one of its great strengths. Take advantage of these tools to better understand your data and optimize your system.

Information Schema is just the beginning of a more advanced set of tools for database management. In upcoming sessions, we will explore equally important topics such as backups, migrations and data visualization with tools like Metabase. Don't miss them!