Table Size vs Index Size

Suresh Ramagiri -

In ClustrixDB all portions of a table are considered an index. Thus when the sizes from in system.table_sizes and system.index_sizes when summed are expected to be the same. However, index_sizes gives more granularity into the makeup of the table which can be useful in some cases.

 The base representation of a table listed as __base unless there is a primary key, in which case the primary key will take the place of the base representation in the index listing. A primary key (which includes the base representation) is noted as __idx_<table_name>__PRIMARY. Additional indexes are listed by name.

Example:

sql> create table foo01 (id int, name varchar);
Query OK, 0 rows affected (0.32 sec)
sql> create table foo02 (id int not null primary key, name varchar);
Query OK, 0 rows affected (0.05 sec)
sql> create table foo03 (id int not null primary key, name varchar, key indexkey (name));
Query OK, 0 rows affected (0.05 sec)
sql> select * from system.table_sizes where `database` = 'example';
+----------+-------+--------+
| Database | Table | Bytes |
+----------+-------+--------+
| example | foo01 | 327680 |
| example | foo02 | 327680 |
| example | foo03 | 655360 |
+----------+-------+--------+
3 rows in set (0.12 sec)
sql> select * from system.index_sizes where `database` = 'example';
+----------+-------+----------------------+--------+
| Database | Table | Index | Bytes |
+----------+-------+----------------------+--------+
| example | foo01 | __base_foo01 | 327680 |
| example | foo02 | __idx_foo02__PRIMARY | 327680 |
| example | foo03 | __idx_foo03__PRIMARY | 327680 |
| example | foo03 | indexkey | 327680 |
+----------+-------+----------------------+--------+
4 rows in set (0.11 sec)

 

If you want to query the system.index_sizes table and not count the base rep or the primary key (obtain the size of just the secondary indicies) you can add the following to the query above:

AND `Index` NOT LIKE '%__base%' AND `Index` NOT LIKE '%__PRIMARY'

Example:

sql> select * from system.index_sizes where `database` = 'example' and `Index` NOT LIKE '%__base%' AND `Index` NOT LIKE '%__PRIMARY';
+----------+-------+----------+--------+
| Database | Table | Index | Bytes |
+----------+-------+----------+--------+
| example | foo03 | indexkey | 327680 |
+----------+-------+----------+--------+
1 row in set (0.11 sec)

 

Have more questions? Submit a request

0 Comments

Please sign in to leave a comment.
Powered by Zendesk