Mysql – What should one do if the auto increment primary key exceeds the maximum

Since you need to go to BIGINT, prepare for Disk Space Growth.

• INT is 4 bytes
• BIGINT is 8 bytes

For every table that uses INT and you want to switch to BIGINT, you must forecast how much additional space to expect.

For example, for figure out how much space mydb.mytable will increase when you shift all INT(10) columns to BIGINT, run this query

SELECT CONCAT('SELECT COUNT(1)*',int_column_count*4,
' SizeIncreaseInBytes FROM ',table_schema,'.',table_name,';') INTO @SQLStmt
FROM (SELECT table_schema,table_name,
COUNT(1) int_column_count from
(
    SELECT table_schema,table_name,column_type
    FROM information_schema.columns
    where table_schema = 'mydb'
    AND table_name = 'mytable'
    AND column_type = 'int(10)'
) AA group by table_schema,table_name) A;
SELECT @SQLStmt;
PREPARE s FROM @SQLStmt;
EXECUTE s; DEALLOCATE PREPARE s;

Let's use the table dev_oxygen.bplv_reg

mysql> show create table dev_oxygen.bplv_reg\G
*************************** 1. row ***************************
       Table: bplv_reg
Create Table: CREATE TABLE `bplv_reg` (
  `id` int(11) NOT NULL DEFAULT '0',
  `firstname` varchar(40) DEFAULT NULL,
  `lastname` varchar(40) DEFAULT NULL,
  `address` varchar(255) DEFAULT NULL,
  `city` varchar(100) DEFAULT NULL,
  `state` varchar(20) DEFAULT NULL,
  `zipcode` varchar(11) DEFAULT NULL,
  `email` varchar(200) NOT NULL,
  `phone` bigint(20) DEFAULT NULL,
  `age` int(10) DEFAULT NULL,
  `newsletter` smallint(6) NOT NULL DEFAULT '0',
  `ip_address` varchar(20) DEFAULT NULL,
  `fb_user_id` int(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

Notice the table has age is int(10)

Here is the table count:

mysql> SELECT COUNT(1) FROM dev_oxygen.bplv_reg;
+----------+
| COUNT(1) |
+----------+
|     2038 |
+----------+
1 row in set (0.00 sec)

mysql>

Now let's compute the number of bytes the table will have to grow:

mysql> SELECT CONCAT('SELECT COUNT(1)*',int_column_count*4,
    -> ' SizeIncreaseInBytes FROM ',table_schema,'.',table_name,';') INTO @SQLStmt
    -> FROM (SELECT table_schema,table_name,
    -> COUNT(1) int_column_count from
    -> (
    ->     SELECT table_schema,table_name,column_type
    ->     FROM information_schema.columns
    ->     where table_schema = 'dev_oxygen'
    ->     AND table_name = 'bplv_reg'
    ->     AND column_type = 'int(10)'
    -> ) AA group by table_schema,table_name) A;
Query OK, 1 row affected (0.00 sec)

mysql> SELECT @SQLStmt;
+-----------------------------------------------------------------+
| @SQLStmt                                                        |
+-----------------------------------------------------------------+
| SELECT COUNT(1)*4 SizeIncreaseInBytes FROM dev_oxygen.bplv_reg; |
+-----------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> PREPARE s FROM @SQLStmt;
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> EXECUTE s; DEALLOCATE PREPARE s;
+--------------------+
| SizeIncreaseInBytes|
+--------------------+
|               8152 |
+--------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql>

This number is just the size increase (in bytes) for the age column.

What about the id? It's not int(10) but int(11). Could you forecast for int(11), Sure, just replace int(10) in the query with int(11).

What about the primary key and what if a primay key has multiple int columns defined as int(11)?

Let's pick a different table called oxygen.history;

mysql> show create table oxygen.history\G
*************************** 1. row ***************************
       Table: history
Create Table: CREATE TABLE `history` (
  `uid` int(11) NOT NULL DEFAULT '0',
  `nid` int(11) NOT NULL DEFAULT '0',
  `timestamp` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`uid`,`nid`),
  KEY `nid` (`nid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> select count(1) from oxygen.history;
+----------+
| count(1) |
+----------+
|      564 |
+----------+
1 row in set (0.00 sec)

mysql>

This table has two columns in the primary key, both columns are int(11)

mysql> select CONCAT('SELECT COUNT(1)*',int_column_count*4,
    -> ' IndexSizeIncreaseInBytes FROM ',table_schema,'.',table_name,';')
    -> INTO @SQLStmt FROM
    -> (SELECT AA.table_schema,AA.table_name,
    -> COUNT(1) int_column_count FROM
    -> (
    ->     select table_schema,table_name,column_name
    ->     from information_schema.columns
    ->     where table_schema ='oxygen'
    ->     and table_name='history'
    ->     and column_type = 'int(11)'
    -> ) AA INNER JOIN
    -> (
    ->     select table_schema,table_name,column_name
    ->     FROM information_schema.STATISTICS
    ->     WHERE INDEX_NAME='PRIMARY'
    -> ) BB
    -> USING (table_schema,table_name,column_name)
    -> GROUP BY AA.table_schema,AA.table_name) A;
Query OK, 1 row affected (0.02 sec)

mysql> SELECT @SQLStmt;
+-----------------------------------------------------------------+
| @SQLStmt                                                        |
+-----------------------------------------------------------------+
| SELECT COUNT(1)*8 IndexSizeIncreaseInBytes FROM oxygen.history; |
+-----------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> PREPARE s FROM @SQLStmt;
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> EXECUTE s; DEALLOCATE PREPARE s;
+--------------------------+
| IndexSizeIncreaseInBytes |
+--------------------------+
|                     4512 |
+--------------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql>

Based on how much increase is forecasted for the primary key, you can now increase these

• MyISAM Key Cache (if you are forecasting for a MyISAM table, sized by key_buffer_size)
• For InnoDB, you combine the two queries above to measure data and index size increases to size up the InnoDB Buffer Pool (size by innodb_buffer_pool_size).