MySQL Table Sizes

Using the INFORMATION_SCHEMA database:

• Connect to your MySQL database.
• Execute the following SQL query:

SELECT TABLE_NAME, TABLE_ROWS, DATA_LENGTH, INDEX_LENGTH,
       DATA_LENGTH + INDEX_LENGTH AS TOTAL_SIZE
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'your_database_name';   

• Replace 'your_database_name' with the actual name of your database.
• The query will return a table with the following columns:
  • TABLE_NAME: The name of the table.
  • TABLE_ROWS: The approximate number of rows in the table.
  • DATA_LENGTH: The size of the data in the table, in bytes.
  • TOTAL_SIZE: The total size of the table, in bytes.

Using the SHOW TABLE STATUS command:

SHOW TABLE STATUS FROM 'your_database_name';

• The query will return a table with information about each table in the database, including the Avg_row_length and Data_length columns.
• You can calculate the approximate total size of a table by multiplying Avg_row_length by TABLE_ROWS and adding Data_length.

Note:

• The TABLE_ROWS column in both methods provides an approximate number of rows. For more accurate counts, you can use the COUNT(*) function.
• The Data_length and Index_length columns in both methods provide the size of the data and indexes stored on disk. The actual size of a table in memory may vary.
• To get the size of a specific table, you can filter the results of either query by specifying the table name in the WHERE clause.

Example Codes for MySQL Table Sizes

Query to get the sizes of all tables in a database:

SELECT TABLE_NAME, TABLE_ROWS, DATA_LENGTH, INDEX_LENGTH,
       DATA_LENGTH + INDEX_LENGTH AS TOTAL_SIZE
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'your_database_name';   

Explanation:

• TABLE_SCHEMA: Replace 'your_database_name' with your actual database name.
• TABLE_ROWS: Approximate number of rows.
• DATA_LENGTH: Size of the data in bytes.

Example with PHP:

<?php
$servername = "your_servername";
$username = "your_username";
$password = "your_password";
$dbname = "your_database_name";

// Create connection
$conn = new mysqli($servername,    $username, $password, $dbname);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);   
}

$sql = "SELECT TABLE_NAME, TABLE_ROWS, DATA_LENGTH, INDEX_LENGTH,
       DATA_LENGTH + INDEX_LENGTH AS TOTAL_SIZE
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA    = '$dbname'";

$result = $conn->query($sql);

if ($result->num_rows > 0) {
    while($row = $result->fetch_assoc())    {
        echo "Table Name: " . $row["TABLE_NAME"]. " - Rows: " . $row["TABLE_ROWS"]. " - Data Length: " . $row["DATA_LENGTH"]. " - Index Length: " . $row["INDEX_LENGTH"] . " - Total Size: " . $row["TOTAL_SIZE"] . "<br>";
    }
} else {
    echo "0 results";
}

$conn->close();
?>

SHOW TABLE STATUS FROM 'your_database_name';

• Avg_row_length: Average row length in bytes.
• Data_length: Data length in bytes.

Example with Python and MySQLdb:

import MySQLdb

# Connect to the database
db = MySQLdb.connect("your_servername", "your_username", "your_password", "your_database_name")

# Prepare a cursor object using the database
cursor = db.cursor()

# Execute the SQL query
cursor.execute("SHOW TABLE STATUS FROM 'your_database_name'")

# Fetch all rows from the query
results = cursor.fetchall()

# Print the results
for row in results:
    print("Table name:", row[0])
    print("Average row length:", row[5])
    print("Data length:", row[6])
    print()

# Close the connection
db.close()

Alternative Methods for Getting MySQL Table Sizes

While the methods described in previous responses are common and effective, here are some additional approaches you can consider:

Using the EXPLAIN command with SELECT *

This method provides a more detailed breakdown of the query execution plan, including the table access type and number of rows examined. Although it doesn't directly give the table size, it can help you identify potential performance bottlenecks that might affect table size.

EXPLAIN SELECT * FROM your_table_name;

This command verifies the consistency of a table. While it doesn't directly provide size information, it can help identify potential corruption issues that might affect table size.

CHECK TABLE your_table_name;

Using a MySQL management tool

Many MySQL management tools, such as phpMyAdmin, MySQL Workbench, and HeidiSQL, provide graphical interfaces that allow you to easily view table sizes and other database information.

Using a custom script or application

You can create a custom script or application that leverages the INFORMATION_SCHEMA database or SHOW TABLE STATUS command to retrieve table size information and present it in a desired format. This approach offers greater flexibility and customization.

Using a performance monitoring tool

Some performance monitoring tools, such as Percona Server Manager or MySQL Enterprise Monitor, can provide detailed information about table sizes and other database metrics.

Choosing the right method:

The best method for you depends on your specific needs and preferences. If you need a quick overview of table sizes, using the INFORMATION_SCHEMA database or a management tool is a good option. For more in-depth analysis or automation, creating a custom script or using a performance monitoring tool might be more suitable.