2

I have a table :

create table a(a1 int, a2 char(10),a3 float);

I can manually count the number of bytes allocated for this table a. But, how to find its size in mysql with some queries???

Strawberry
  • 33,750
  • 13
  • 40
  • 57
  • *I cam manually count the number of bytes allocated for this table a.* Really? – Akina Sep 18 '20 at 08:42
  • Yeah it's 4+4+10. 18 bytes –  Sep 18 '20 at 08:43
  • are you looking for total space taken by table a? – Derviş Kayımbaşıoğlu Sep 18 '20 at 08:44
  • https://chartio.com/resources/tutorials/how-to-get-the-size-of-a-table-in-mysql/ – spike 王建 Sep 18 '20 at 08:47
  • Does this answer your question? [How to get the sizes of the tables of a MySQL database?](https://stackoverflow.com/questions/9620198/how-to-get-the-sizes-of-the-tables-of-a-mysql-database) – Sebastian Brosch Sep 18 '20 at 08:47
  • 1
    *Yeah it's 4+4+10. 18 bytes* First, this is wrong. You have not taken a lot of bytes into account - nullability bytes and inner row number bytes at least. Second, the size of row is relative to the bytes allocated to a table very-very approximate. Third, the indices needs in a space also. And so on... – Akina Sep 18 '20 at 08:48

1 Answers1

2

IF you are looking for physical table size of table a

SELECT
  TABLE_NAME AS `Table`,
  ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024) AS `Size (MB)`
FROM
  information_schema.TABLES
WHERE
    TABLE_SCHEMA = '<schema>'
  AND
    TABLE_NAME = 'a'
ORDER BY
  (DATA_LENGTH + INDEX_LENGTH)
DESC;
Derviş Kayımbaşıoğlu
  • 28,492
  • 4
  • 50
  • 72
  • ```SELECT a AS `Table`, ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024) AS `Size (MB)` FROM information_schema.TABLES WHERE TABLE_SCHEMA = "" AND TABLE_NAME = "a" ORDER BY (DATA_LENGTH + INDEX_LENGTH) DESC; ERROR 1054 (42S22): Unknown column 'a' in 'field list' ``` Reason for this error please. –  Sep 18 '20 at 16:15
  • Dude, I too had this doubt. I checked your edits. It displayed ```empty set```. What to do??? Any help?? – Sanmitha Sadhishkumar Sep 20 '20 at 06:42
  • It doesn't make sense. comment the --TABLE_SCHEMA part and change the TABLE_NAME part from equality to like operator so that you can make wider searchç – Derviş Kayımbaşıoğlu Sep 20 '20 at 19:01