113

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.

BenMorel
  • 34,448
  • 50
  • 182
  • 322
Brian G
  • 53,704
  • 58
  • 125
  • 140

8 Answers8

133

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;
Daniel Zohar
  • 1,962
  • 2
  • 13
  • 19
115

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

Emmett
  • 14,035
  • 12
  • 56
  • 81
Josh Warner-Burke
  • 1,367
  • 1
  • 10
  • 4
  • 2
    Avg_row_length, Data_length, Max_data_length, Index_length, Data_free are all reported in bytes, yes. – Luke Rehmann Jul 23 '14 at 13:41
  • 39
    This gives the *total* index size, not a size per index (assuming a table has multiple indices). – Sai Apr 08 '15 at 14:54
60

If you are using InnoDB tables, you can get the size for individual indexes from mysql.innodb_index_stats. The 'size' stat contains the answer, in pages, so you have to multiply it by the page-size, which is 16K by default. 

select database_name, table_name, index_name, stat_value*@@innodb_page_size
from mysql.innodb_index_stats where stat_name='size';
Vajk Hermecz
  • 5,413
  • 2
  • 34
  • 25
  • 1
    Fantastic. Exactly what I was looking for! – Dave Martorana Apr 05 '16 at 00:45
  • 6
    To clarify: With this query, the `stat_value` is already multiplied by the page-size, so the column gives the index size in bytes. – Benedikt Köppel Oct 25 '16 at 12:00
  • 4
    Thank you, much better than the accepted answer. A note for other noobs like me - database_name, table_name and index_name are not to be replaced with your real database name and table name ;) instead the command should be used exactly as it is. – luben Jul 27 '17 at 08:12
  • in my case it gave a very different (and unrealistic) size from the one reported by `show table status from [dbname]` – architectonic Jul 28 '17 at 07:41
9

Here's an adaption from some of the above to also give you the percentage of the total index for the table that each index has used, hopefully this will be useful for someone

select 
    database_name, 
    table_name, 
    index_name, 
    round((stat_value*@@innodb_page_size)/1024/1024, 2) SizeMB, 
    round(((100/(SELECT INDEX_LENGTH FROM INFORMATION_SCHEMA.TABLES t WHERE t.TABLE_NAME = iis.table_name and t.TABLE_SCHEMA = iis.database_name))*(stat_value*@@innodb_page_size)), 2) `Percentage`
from mysql.innodb_index_stats iis 
where stat_name='size' 
and table_name = 'TargetTable'
and database_name = 'targetDB'

Example output

database_name   table_name  index_name  SizeMB  Percentage
targetDB        TargetTable id          10      55.55
targetDB        TargetTable idLookup    5       27.77
targetDB        TargetTable idTest      3       16.66

Regards Liam

Liam Wheldon
  • 725
  • 1
  • 5
  • 19
  • I have a particular case in which for a PK indexes the Percentage shows values over 100, like 1844 or 677. I guess there's a problem with the query for those cases. – Alex P. Mar 09 '19 at 09:49
6

On MyISAM, each index block is 4 KB page filled up to fill_factor with index records, each being key length + 4 bytes long.

Fill factor is normally 2/3

As for InnoDB, the table is always clustered on the PRIMARY KEY, there is no separate PRIMARY KEY index

Quassnoi
  • 413,100
  • 91
  • 616
  • 614
2

Using phpMyAdmin, when viewing the table structure there is a Details link at the bottom somewhere. Once you click on it it will show you the total size of the indexes you have on the table where it is marked Space Usage.

I don't think it shows you each index individually though.

Peter D
  • 4,851
  • 2
  • 30
  • 30
1

From the MySQL 5.6 reference

SELECT SUM(stat_value) pages, index_name,
SUM(stat_value)*@@innodb_page_size size
FROM mysql.innodb_index_stats WHERE table_name='t1'
AND stat_name = 'size' GROUP BY index_name;
viggy28
  • 760
  • 1
  • 10
  • 21
-1

If you just want to know how much space an existing index needs per row, you can have a look at the key_len output from the EXPLAIN command, which shows the index size in bytes:

EXPLAIN SELECT * FROM table_name WHERE column_1 = A AND column_2 = B;

If the query would return at least 1 row, this will show the size of an index on (column_1, column_2). You can validate the correct index was used in the key column of the output.

Synthesis
  • 463
  • 4
  • 5