SQL stands for Structured Query Language which is a computer language for storing, manipulating, and retrieving data stored in relational database management systems (RDBMS). SQL was developed at IBM by Donald Chamberlin, Donald C. Messerli, and Raymond F. Boyce in the year 1970s. MySQL is an open-source Relational Database Management System that stores data in a structured format using rows and columns. MYSQL language is easy to use as compared to other programming languages like C, C++, Java, etc. By learning some basic commands we can work, create, and interact with the Database.
A table is used to organize data in the form of rows and columns and is used for both storing and displaying records in the structure format. It is similar to worksheets in the spreadsheet application. A table creation command requires three things:
- Name of the table
- Names of fields
- Definitions for each field
MySQL allows us to create a table in the database mainly in two ways:
- MySQL Command Line Client
- MySQL Workbench
To get the size of a MySQL database, you can use various methods, including SQL queries or checking the file system. Here are a few ways to accomplish this:
Method 1: Using SQL Query
You can use the information_schema database to query the size of a MySQL database:
SELECT table_schema AS "Database Name",
SUM(data_length + index_length) / (1024 * 1024) AS "Size (MB)"
FROM information_schema.tables
GROUP BY table_schema;
- The query “SELECT table_schema AS ‘Database Name‘” retrieves the information from the column named table_schema in the information_schema.tables. This renaming of this column to “Database Name” is carried out specifically for generating a result set containing each database’s name present within it.
- The “SUM(data_length + index_length) / (1024 * 1024) AS “Size (MB)” code calculates the size of databases by adding their data_length and index_length columns from information_schema.tables. The resultant sum is divided by (1024 * 1024) to convert bytes into megabytes, which is then labeled as “Size (MB)” in the output set.
- The information_schema.tables can be utilized to obtain data on the specified table name. This specific case refers to the information_schema.tables table.
- GROUP BY table_schema: by using the GROUP BY clause on the table_schema column (which holds database names), our query calculates and groups together all sizes of unique databases. The resulting output displays each MySQL database’s name alongside its size in megabytes – obtained by combining both data and index lengths from tables within mentioned database.
In summary, the query retrieves the size of each database in megabytes, combining the data and index lengths of its tables. The result set will display the “Database Name” and the corresponding “Size (MB)” for each database on your MySQL.
Output:
This query sums up the data length and index length for each table in the specified database, providing the total size in MB.
Method 2: Using MySQL
If you’re using the MySQL Shell, you can run the following command:
SHOW TABLE STATUS LIKE ‘DB NAME’;
Example:
SHOW TABLE STATUS LIKE 'student';
- SHOW TABLE STATUS: query tells MySQL to display information about tables.
- LIKE ‘DB NAME’: The LIKE statement is utilized to screen search outcomes using particular patterns. It can be used to filter tables that correspond with the designated database name by utilizing ‘DB NAME’. Simply replace it with the real name of the database you plan on examining.
Output:
Explanation:
This command will display detailed information about each table in the specified database, including the data length and index length.
The result will provide information about all the table in the specified database, details such as the table name, engine, version, row count, data length, index length, etc.
Please Note that the output of this query can be very Large, so you might want to focus on the particular columns that are most relevant according to your need.
Method 3: Third-Party DB Tools
There are few third-party tools available that can provide the size and health of your MySQL databases. Some popular tools are:
- MySQL Workbench
- phpMyAdmin, and others.
Conclusion
Choose the method that best fits your needs and the tools available in your environment. Keep in mind that the SQL queries provide more accurate information about the logical size of the data, while file system inspection provides information about the physical size on disk. You may get large information which is not needed so filter it out according to your need. MySQL Workbench or executing SQL queries and command line commands, understanding your database size helps you make informed decisions about resource allocation and data management.