MySQL auto_increment capacity calculation

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.

  • 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”.