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.

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

Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.
You need to agree with the terms to proceed

Menu