Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a primary key for a table with the following structure,

**Field**          **Type**                  
  nid     int(10)unsigned

I know the maximum limit of int(10) is 4294967295. But What happens if it exceeds the maximum? Should i have to change to bigint ? if so what is the best practice is it to use int or big int in primary key?

share|improve this question
3  
Short answer is yes, change it to BIGINT, when you are approaching the limit. –  ypercube Oct 5 '12 at 8:57
add comment

1 Answer

up vote 2 down vote accepted

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).
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.