Menu
I want to determine the size of my indexes, they are primary key indexes. This happens to be on mysql cluster but I don't think that is significant.
481
In MySQL, an index block is usually 1,024 bytes and the data pointer is usually four bytes. For a 500,000-row table with a key value length of three bytes (the size of MEDIUMINT ), the formula indicates log(500,000)/log(1024/3*2/(3+4)) + 1 = 4 seeks.
Query to check index size in Oracleselect sum(bytes)/1024/1024 as "Index Size (MB)" from dba_segments where segment_name='&INDEX_NAME'; select sum(bytes)/1024/1024 as "Index Size (MB)" from user_segments where segment_name='&INDEX_NAME';
To check the sizes of all of your databases, at the mysql> prompt type the following command: Copy SELECT table_schema AS "Database", ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS "Size (MB)" FROM information_schema.
You can list a table's indexes with the mysqlshow -k db_name tbl_name command. In MySQL 8.0. 30 and later, SHOW INDEX includes the table's generated invisible key, if it has one, by default.
I think this is what you're looking for.
show table status from [dbname] http://dev.mysql.com/doc/refman/5.1/en/show-table-status.html
67
Extending Vajk Hermecz's answer.
This is how you can get all of the indexes size, in megabytes, without the PRIMARY (which is the table itself), ordered by size.
SELECT database_name, table_name, index_name, ROUND(stat_value * @@innodb_page_size / 1024 / 1024, 2) size_in_mb FROM mysql.innodb_index_stats WHERE stat_name = 'size' AND index_name != 'PRIMARY' ORDER BY size_in_mb DESC; If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
