{"id":3421,"date":"2011-04-05T07:36:56","date_gmt":"2011-04-05T05:36:56","guid":{"rendered":"http:\/\/code.openark.org\/blog\/?p=3421"},"modified":"2011-04-05T10:27:20","modified_gmt":"2011-04-05T08:27:20","slug":"checking-for-auto_increment-capacity-with-single-query","status":"publish","type":"post","link":"https:\/\/code.openark.org\/blog\/mysql\/checking-for-auto_increment-capacity-with-single-query","title":{"rendered":"Checking for AUTO_INCREMENT capacity with single query"},"content":{"rendered":"

Darn!<\/em> This means oak-show-limits<\/a> becomes redundant. Am I not supposed to speak about it on my coming presentation<\/a>? Bad timing!<\/p>\n

You have AUTO_INCREMENT<\/strong> columns. How far are you pushing the limits? Are you going to run out of AUTO_INCREMENT<\/strong> values soon? Perhaps you wonder whether you should ALTER<\/strong> from INT<\/strong> to BIGINT<\/strong>?<\/p>\n

The answer is all there in INFORMATION_SCHEMA<\/strong>. The TABLES<\/strong> table shows the current AUTO_INCREMENT<\/strong> value per table, and the COLUMNS<\/strong> table tells us all about a column’s data type.<\/p>\n

It takes some ugly code to deduce the maximum value per column type, what with signed\/unsigned and data type, but then its very simple. Here is the query:<\/p>\n

\n
SELECT\r\n  TABLE_SCHEMA,\r\n  TABLE_NAME,\r\n  COLUMN_NAME,\r\n  DATA_TYPE,\r\n  COLUMN_TYPE,\r\n  IF(\r\n    LOCATE('unsigned', COLUMN_TYPE) > 0,\r\n    1,\r\n    0\r\n  ) AS IS_UNSIGNED,\r\n  (\r\n    CASE DATA_TYPE\r\n      WHEN 'tinyint' THEN 255\r\n      WHEN 'smallint' THEN 65535\r\n      WHEN 'mediumint' THEN 16777215\r\n      WHEN 'int' THEN 4294967295\r\n      WHEN 'bigint' THEN 18446744073709551615\r\n    END >> IF(LOCATE('unsigned', COLUMN_TYPE) > 0, 0, 1)\r\n  ) AS MAX_VALUE,\r\n  AUTO_INCREMENT,\r\n  AUTO_INCREMENT \/ (\r\n    CASE DATA_TYPE\r\n      WHEN 'tinyint' THEN 255\r\n      WHEN 'smallint' THEN 65535\r\n      WHEN 'mediumint' THEN 16777215\r\n      WHEN 'int' THEN 4294967295\r\n      WHEN 'bigint' THEN 18446744073709551615\r\n    END >> IF(LOCATE('unsigned', COLUMN_TYPE) > 0, 0, 1)\r\n  ) AS AUTO_INCREMENT_RATIO\r\nFROM\r\n  INFORMATION_SCHEMA.COLUMNS\r\n  INNER JOIN INFORMATION_SCHEMA.TABLES USING (TABLE_SCHEMA, TABLE_NAME)\r\nWHERE\r\n  TABLE_SCHEMA NOT IN ('mysql', 'INFORMATION_SCHEMA', 'performance_schema')\r\n  AND EXTRA='auto_increment'\r\n;\r\n<\/pre>\n<\/blockquote>\n
There’s one row in the result set for each AUTO_INCREMENT<\/strong> column. since at most one AUTO_INCREMENT<\/strong> column can exist for any given table, each row also identifies a unique table. Resulting columns are mostly self-explanatory, but here’s some details on some of the columns:<\/p>\n
    \n
  • IS_UNSIGNED<\/strong>: 1<\/strong> when the column is UNSIGNED<\/strong>, 0<\/strong> otherwise.<\/li>\n
  • MAX_VALUE<\/strong>: maximum value that can be contained within column.<\/li>\n
  • AUTO_INCREMENT<\/strong>: current AUTO_INCREMENT<\/strong> value for table.<\/li>\n
  • AUTO_INCREMENT_RATIO<\/strong>: value in the range [0..1]<\/strong>, where 1<\/strong> means “100% full”.<\/li>\n<\/ul>\n
    A sample output:<\/p>\n
    \n
    +--------------+------------+--------------+-----------+-----------------------+-------------+------------+----------------+----------------------+\r\n| TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME  | DATA_TYPE | COLUMN_TYPE           | IS_UNSIGNED | MAX_VALUE  | AUTO_INCREMENT | AUTO_INCREMENT_RATIO |\r\n+--------------+------------+--------------+-----------+-----------------------+-------------+------------+----------------+----------------------+\r\n| sakila       | actor      | actor_id     | smallint  | smallint(5) unsigned  |           1 |      65535 |            201 |               0.0031 |\r\n| sakila       | address    | address_id   | smallint  | smallint(5) unsigned  |           1 |      65535 |            606 |               0.0092 |\r\n| sakila       | category   | category_id  | tinyint   | tinyint(3) unsigned   |           1 |        255 |             17 |               0.0667 |\r\n| sakila       | city       | city_id      | smallint  | smallint(5) unsigned  |           1 |      65535 |            601 |               0.0092 |\r\n| sakila       | country    | country_id   | smallint  | smallint(5) unsigned  |           1 |      65535 |            110 |               0.0017 |\r\n| sakila       | customer   | customer_id  | smallint  | smallint(5) unsigned  |           1 |      65535 |            600 |               0.0092 |\r\n| sakila       | film       | film_id      | smallint  | smallint(5) unsigned  |           1 |      65535 |           1001 |               0.0153 |\r\n| sakila       | inventory  | inventory_id | mediumint | mediumint(8) unsigned |           1 |   16777215 |           4582 |               0.0003 |\r\n| sakila       | language   | language_id  | tinyint   | tinyint(3) unsigned   |           1 |        255 |              7 |               0.0275 |\r\n| sakila       | payment    | payment_id   | smallint  | smallint(5) unsigned  |           1 |      65535 |          16050 |               0.2449 |\r\n| sakila       | rental     | rental_id    | int       | int(11)               |           0 | 2147483647 |          16050 |               0.0000 |\r\n| sakila       | staff      | staff_id     | tinyint   | tinyint(3) unsigned   |           1 |        255 |              3 |               0.0118 |\r\n| sakila       | store      | store_id     | tinyint   | tinyint(3) unsigned   |           1 |        255 |              3 |               0.0118 |\r\n+--------------+------------+--------------+-----------+-----------------------+-------------+------------+----------------+----------------------+\r\n<\/pre>\n<\/blockquote>\n
    Bonus: free advice on increasing your AUTO_INCREMENT capacity<\/h4>\n
    Make it UNSIGNED<\/strong>. No, really. Check your definitions now.<\/p>\n","protected":false},"excerpt":{"rendered":"
    Darn! This means oak-show-limits becomes redundant. Am I not supposed to speak about it on my coming presentation? Bad timing! You have AUTO_INCREMENT columns. How far are you pushing the limits? Are you going to run out of AUTO_INCREMENT values soon? Perhaps you wonder whether you should ALTER from INT to BIGINT? The answer is […]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"enabled":false},"version":2}},"categories":[5],"tags":[25,24,34],"class_list":["post-3421","post","type-post","status-publish","format-standard","hentry","category-mysql","tag-analysis","tag-information_schema","tag-openark-kit"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p2bZZp-Tb","_links":{"self":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/3421","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/comments?post=3421"}],"version-history":[{"count":24,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/3421\/revisions"}],"predecessor-version":[{"id":3470,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/3421\/revisions\/3470"}],"wp:attachment":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/media?parent=3421"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/categories?post=3421"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/tags?post=3421"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}