Calculate PK usage for tables with composite key as Primary key

MySQL & MariaDB Percona Server for MySQL 5.7
1

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

2

Hi Virin,
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?

Thanks,
K

3

It depends on which character set you are using. latin1 uses 1 byte per character. utf8/utf8mb3 can use 1-3 bytes, and utf8mb4 uses up to 4 bytes.

4

Hi Kedar,
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)

5

using utf8 here mat, just trying to know usage depending on number of rows

6

Hi Virin,

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…

Thanks,
K.