Hi, I am trying to get primary % used on table in percona mysql 5.7 version db, it is straight when there a only one column as Primary Key.
but, what is correct approach when ,
I have table where primary is on two columns and each column have different datatypes.
PRIMARY KEY ( ‘column A’,‘column B’)
column A mediumint(5) unsigned
column B varchar(25)
In this case , what is correct way to calculate PK space usage for this table
Imagine InnoDB table as a PK arranged B-tree… it is clustered index storing all the data in sorted order… So your table size is your primary index size. If you add secondary indexes, you will see the size of the other indexes in the index_length column of “show table status” output.
I wonder though why would you try to find the PK space usage?
Just trying to know the usage to see if any tables have more usage and might run to issue related to PK usage (if auto increment is not set etc)
beside that, just curious to find how to get usage estimation on tables with a composite key as primary key (more than one column with different datatypes)
If it is auto-increment or integer type, you call still find the fill factor you may query OR use PMM.
In your case where auto-increment is not set BUT you have a composite primary key with a varchar column. I really see no way to identify a limit or range for it’s usage. Even if it is two integers the domain for that primary key will become really large: say n! / (m!(n - m)!) assuming that’s the range of those two columns respectively!
Best is to monitor (pmm) and routinely check the auto-increment fill depending on how much is your data growth…