How To Get The Size Of A Table In MySQL
Short Answer
1
2
3
4
5
SELECT
table_name AS `Table`,
round(((data_length + index_length) / 1024 / 1024), 2) AS `Size in MB`
FROM information_schema.TABLES
WHERE table_name = "TABLE_NAME";
Substitute the string TABLE_NAME with the name of the table we would like to check the size.
Example
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
-- create
CREATE TABLE EMPLOYEE (
empId INTEGER PRIMARY KEY,
name TEXT NOT NULL,
dept TEXT NOT NULL
);
-- insert
INSERT INTO EMPLOYEE VALUES (0001, 'Clark', 'Sales');
INSERT INTO EMPLOYEE VALUES (0002, 'Dave', 'Accounting');
INSERT INTO EMPLOYEE VALUES (0003, 'Ava', 'Sales');
-- fetch
SELECT
table_name AS `Table`,
round(((data_length + index_length) / 1024 / 1024), 2) AS `Size in MB`
FROM information_schema.TABLES
WHERE table_name = "EMPLOYEE";
Table EMPLOYEE:
1
2
3
4
5
6
7
+-------+-------+------------+
| empId | name | dept |
+-------+-------+------------+
| 1 | Clark | Sales |
| 2 | Dave | Accounting |
| 3 | Ava | Sales |
+-------+-------+------------+
Query output:
1
2
3
4
5
+----------+------------+
| Table | Size in MB |
+----------+------------+
| EMPLOYEE | 0.02 |
+----------+------------+