MySQL auto_increment capacity calculation

26th Dec 2020

auto_increment, information_schema, mysql

Have you ever wondered if your tables in a MySQL database may be nearing capacity on INT AUTO_INCREMENT columns?First things first: If your application design allows, set the AUTO_INCREMENT column to be UNSIGNED. It effectively doubles it’s capacity.

The following query on INFORMATION_SCHEMA provides a convenient way to verify your usage. Only top 100 columns are listed but you can change the query to suit your requirements.

IS_UNSIGNED:
1 = column is UNSIGNED
0 = column is SIGNED
MAX_VALUE: maximum integer value that can be stored in the column.
AUTO_INCREMENT: current AUTO_INCREMENT value for the column.
AUTO_INCREMENT_RATIO: value in the range [0..1], where 1 means “100% full”.

SELECT
  TABLE_SCHEMA,
  TABLE_NAME,
  COLUMN_NAME,
  DATA_TYPE,
  COLUMN_TYPE,
  IF(
    LOCATE('unsigned', COLUMN_TYPE) > 0,
    1,
    0
  ) AS IS_UNSIGNED,
  (
    CASE DATA_TYPE
      WHEN 'tinyint' THEN 255
      WHEN 'smallint' THEN 65535
      WHEN 'mediumint' THEN 16777215
      WHEN 'int' THEN 4294967295
      WHEN 'bigint' THEN 18446744073709551615
    END >> IF(LOCATE('unsigned', COLUMN_TYPE) > 0, 0, 1)
  ) AS MAX_VALUE,
  AUTO_INCREMENT,
  AUTO_INCREMENT / (
    CASE DATA_TYPE
      WHEN 'tinyint' THEN 255
      WHEN 'smallint' THEN 65535
      WHEN 'mediumint' THEN 16777215
      WHEN 'int' THEN 4294967295
      WHEN 'bigint' THEN 18446744073709551615
    END >> IF(LOCATE('unsigned', COLUMN_TYPE) > 0, 0, 1)
  ) AS AUTO_INCREMENT_RATIO
FROM
  INFORMATION_SCHEMA.COLUMNS
  INNER JOIN INFORMATION_SCHEMA.TABLES USING (TABLE_SCHEMA, TABLE_NAME)
WHERE
  TABLE_SCHEMA NOT IN ('mysql', 'INFORMATION_SCHEMA', 'performance_schema')
  AND EXTRA='auto_increment'
ORDER BY AUTO_INCREMENT_RATIO DESC
LIMIT 100
;

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

SELECT

TABLE_SCHEMA,

TABLE_NAME,

COLUMN_NAME,

DATA_TYPE,

COLUMN_TYPE,

IF(

LOCATE('unsigned', COLUMN_TYPE) > 0,

1,

0

) AS IS_UNSIGNED,

(

CASE DATA_TYPE

WHEN 'tinyint' THEN 255

WHEN 'smallint' THEN 65535

WHEN 'mediumint' THEN 16777215

WHEN 'int' THEN 4294967295

WHEN 'bigint' THEN 18446744073709551615

END >> IF(LOCATE('unsigned', COLUMN_TYPE) > 0, 0, 1)

) AS MAX_VALUE,

AUTO_INCREMENT,

AUTO_INCREMENT / (

CASE DATA_TYPE

WHEN 'tinyint' THEN 255

WHEN 'smallint' THEN 65535

WHEN 'mediumint' THEN 16777215

WHEN 'int' THEN 4294967295

WHEN 'bigint' THEN 18446744073709551615

END >> IF(LOCATE('unsigned', COLUMN_TYPE) > 0, 0, 1)

) AS AUTO_INCREMENT_RATIO

FROM

INFORMATION_SCHEMA.COLUMNS

INNER JOIN INFORMATION_SCHEMA.TABLES USING (TABLE_SCHEMA, TABLE_NAME)

WHERE

TABLE_SCHEMA NOT IN ('mysql', 'INFORMATION_SCHEMA', 'performance_schema')

AND EXTRA='auto_increment'

ORDER BY AUTO_INCREMENT_RATIO DESC

LIMIT 100

;

MySQL auto_increment capacity calculation

Recent Posts

Categories

Tag cloud

Archives