How to get the sizes of the tables of a MySQL database

0 votes

I can run this query to get the sizes of all tables in a MySQL database:

show table status from myDatabaseName;

I would like some help in understanding the results. I am looking for tables with the largest sizes.

Which column should I look at?

Aug 18, 2020 in PHP by kartik
• 37,510 points
133 views

1 answer to this question.

0 votes

Hello @kartik,

You can use this query to show the size of a table:

SELECT 
    table_name AS `Table`, 
    round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB` 
FROM information_schema.TABLES 
WHERE table_schema = "$DB_NAME"
    AND table_name = "$TABLE_NAME";

or this query to list the size of every table in every database, largest first:

SELECT 
     table_schema as `Database`, 
     table_name AS `Table`, 
     round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB` 
FROM information_schema.TABLES 
ORDER BY (data_length + index_length) DESC;

Hope it helps!!
Thank You!

answered Aug 18, 2020 by Niroj
• 82,720 points

Related Questions In PHP

0 votes
1 answer

How do I get the last inserted ID of a MySQL table in PHP?

Hello @kartik, If you're using PDO, use PDO::lastInsertId. If you're ...READ MORE

answered Oct 22, 2020 in PHP by Niroj
• 82,720 points
175 views
0 votes
1 answer

How to get the list of specific files in a directory using php?

Hello @kartik, You'll be wanting to use glob() Example: $files = ...READ MORE

answered Nov 6, 2020 in PHP by Niroj
• 82,720 points
86 views
0 votes
1 answer

How to retrieve or obtain data from the MySQL database using PHP?

Hello kartik,  Actually there are many functions that  ...READ MORE

answered Mar 27, 2020 in PHP by Niroj
• 82,720 points
429 views
0 votes
1 answer

How can I get ID of the last updated row in MySQL?

Hello @kartik, Suppose, item_id is an integer identity column in items table ...READ MORE

answered Apr 9, 2020 in PHP by Niroj
• 82,720 points
3,617 views
+1 vote
1 answer

How to make anchor tag with routing using Laravel?

Hey @kartik, First you have to go to ...READ MORE

answered Mar 18, 2020 in Laravel by Niroj
• 82,720 points
7,093 views
0 votes
1 answer

What is redirection in Laravel?

Named route is used to give specific ...READ MORE

answered Mar 18, 2020 in Laravel by Niroj
• 82,720 points
812 views
0 votes
1 answer

How to install Laravel via composer?

Hello, This is simple you just need to ...READ MORE

answered Mar 23, 2020 in Laravel by Niroj
• 82,720 points
627 views
+1 vote
1 answer

What are named routes in Laravel and How can specify route names for controller actions?

Hey @kartik, Named routing is another amazing feature of ...READ MORE

answered Mar 23, 2020 in Laravel by Niroj
• 82,720 points
7,498 views
0 votes
1 answer

How to get a list of user accounts using the command line in MySQL?

Hello @kartik, Use this query: SELECT User FROM mysql.user; Which ...READ MORE

answered Aug 17, 2020 in PHP by Niroj
• 82,720 points
142 views
0 votes
1 answer

How to get all columns' names for all the tables in MySQL?

Hello @kartik, Try this: select * from information_schema.columns where table_schema ...READ MORE

answered Aug 19, 2020 in PHP by Niroj
• 82,720 points
114 views