The size of a table and an index

{question}

How do I check the size of a table on disk and in memory?

How do I check the size of the indexes?

{question}

{answer}

How do I check the size of a Rowstore table?

Rowstore tables will use the space in memory. To view the usage of the Rowstore tables, you can use the following query:

SELECT `database_name`, `table_name`, FORMAT(SUM(`memory_use`) / 1024/1024, 0) MB
FROM `information_schema`.`table_statistics`
GROUP BY 1, 2;

+---------------+--------------------+-------+
| database_name | table_name         | MB    |
+---------------+--------------------+-------+
| <database>    | <rowstoretable>    | 1,689 |
| <database>    | <columnstoretable> | 0     |
+---------------+--------------------+-------+
2 rows in set (0.10 sec)

SELECT `database_name`, `table_name`, FORMAT(SUM(`memory_use`) / 1024/1024, 0) MB
FROM `information_schema`.`table_statistics`
WHERE `database_name` = '<dbname>'
AND table_name = '<tablename>'
GROUP BY 1, 2;

SELECT `database_name`, `table_name`, 
    FORMAT(SUM(`uncompressed_size`) / 1024/1024, 0) `uncompressed_mb`, 
    FORMAT(SUM(`compressed_size`) / 1024/1024, 0) `compressed_mb`
FROM `information_schema`.`columnar_segments`
GROUP BY 1, 2;

+---------------+------------+-----------------+---------------+
| database_name | table_name | uncompressed_mb | compressed_mb |
+---------------+------------+-----------------+---------------+
| <database>    | <table>    | 642             | 63            |
+---------------+------------+-----------------+---------------+
1 rows in set (0.10 sec)

There will only be data presented for the columnstore tables.

For a particular database or table, you can use a WHERE clause:

SELECT `database_name`, `table_name`, 
    FORMAT(SUM(`uncompressed_size`) / 1024/1024, 0) `uncompressed_mb`, 
    FORMAT(SUM(`compressed_size`) / 1024/1024, 0) `compressed_mb`
FROM `information_schema`.`columnar_segments`
WHERE `database_name` = '<dbname>'
AND table_name = '<tablename>'
GROUP BY 1, 2;

You can view more information from the system table columnar_segments in our documentation.

How do I check the size of the Indexes?

View the size of the indexes by using the following query:

SELECT `database_name`, `table_name`, `index_name`, FORMAT(SUM(`MEMORY_USE`) / 1024/1024, 0) memoryUseMB
FROM `information_schema`.INDEX_STATISTICS
GROUP BY 1, 2, 3;

+---------------+------------------+-------------+-------------+
| database_name | table_name       | index_name  | memoryUseMB |
+---------------+------------------+-------------+-------------+
| <database>    | <table>          | PRIMARY     | 1,342       |
| <database>    | <table>          | <indexName> | 839         |
+---------------+------------------+-------------+-------------+
2 rows in set (0.12 sec)

If you want to see from a particular database, table, or index, you can use a WHERE clause:

SELECT `database_name`, `table_name`, `index_name`, FORMAT(SUM(`MEMORY_USE`) / 1024/1024, 0) memoryUseMB
FROM `information_schema`.INDEX_STATISTICS
WHERE `database_name` = '<database>'
AND `table_name` = '<tablename>'
AND `index_name` = '<indexname>'
GROUP BY 1, 2, 3;

For the PRIMARY key, the `index_name` will always be PRIMARY.

The results are divided by 1024/1024 to return the results in MB.

How do I check the size of the Hash Indexes for a columnstore table?

select sum(compressed_size)+mcf.sum_size as hash_index_size
from information_schema.mv_columnar_segment_index,
    (select sum(size) as sum_size
     from information_schema.mv_columnstore_files
     where file_type in ('CROSS_SEGMENT_HASH_INDEX')
     and database_name = '<database>'
     and table_name = '<table_name>') mcf
where database_name = '<database>'
  and table_name = '<table_name>';

+-----------------+
| hash_index_size |
+-----------------+
| 67281736        |
+-----------------+

{answer}