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 all there in INFORMATION_SCHEMA. The TABLES table shows the current AUTO_INCREMENT value per table, and the COLUMNS table tells us all about a column’s data type.
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:
SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, DATA_TYPE, COLUMN_TYPE, IF( LOCATE('unsigned', COLUMN_TYPE) > 0, 1, 0 ) AS IS_UNSIGNED, ( CASE DATA_TYPE WHEN 'tinyint' THEN 255 WHEN 'smallint' THEN 65535 WHEN 'mediumint' THEN 16777215 WHEN 'int' THEN 4294967295 WHEN 'bigint' THEN 18446744073709551615 END >> IF(LOCATE('unsigned', COLUMN_TYPE) > 0, 0, 1) ) AS MAX_VALUE, AUTO_INCREMENT, AUTO_INCREMENT / ( CASE DATA_TYPE WHEN 'tinyint' THEN 255 WHEN 'smallint' THEN 65535 WHEN 'mediumint' THEN 16777215 WHEN 'int' THEN 4294967295 WHEN 'bigint' THEN 18446744073709551615 END >> IF(LOCATE('unsigned', COLUMN_TYPE) > 0, 0, 1) ) AS AUTO_INCREMENT_RATIO FROM INFORMATION_SCHEMA.COLUMNS INNER JOIN INFORMATION_SCHEMA.TABLES USING (TABLE_SCHEMA, TABLE_NAME) WHERE TABLE_SCHEMA NOT IN ('mysql', 'INFORMATION_SCHEMA', 'performance_schema') AND EXTRA='auto_increment' ;
There’s one row in the result set for each AUTO_INCREMENT column. since at most one AUTO_INCREMENT 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:
- IS_UNSIGNED: 1 when the column is UNSIGNED, 0 otherwise.
- MAX_VALUE: maximum value that can be contained within column.
- AUTO_INCREMENT: current AUTO_INCREMENT value for table.
- AUTO_INCREMENT_RATIO: value in the range [0..1], where 1 means “100% full”.
A sample output:
+--------------+------------+--------------+-----------+-----------------------+-------------+------------+----------------+----------------------+ | TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | DATA_TYPE | COLUMN_TYPE | IS_UNSIGNED | MAX_VALUE | AUTO_INCREMENT | AUTO_INCREMENT_RATIO | +--------------+------------+--------------+-----------+-----------------------+-------------+------------+----------------+----------------------+ | sakila | actor | actor_id | smallint | smallint(5) unsigned | 1 | 65535 | 201 | 0.0031 | | sakila | address | address_id | smallint | smallint(5) unsigned | 1 | 65535 | 606 | 0.0092 | | sakila | category | category_id | tinyint | tinyint(3) unsigned | 1 | 255 | 17 | 0.0667 | | sakila | city | city_id | smallint | smallint(5) unsigned | 1 | 65535 | 601 | 0.0092 | | sakila | country | country_id | smallint | smallint(5) unsigned | 1 | 65535 | 110 | 0.0017 | | sakila | customer | customer_id | smallint | smallint(5) unsigned | 1 | 65535 | 600 | 0.0092 | | sakila | film | film_id | smallint | smallint(5) unsigned | 1 | 65535 | 1001 | 0.0153 | | sakila | inventory | inventory_id | mediumint | mediumint(8) unsigned | 1 | 16777215 | 4582 | 0.0003 | | sakila | language | language_id | tinyint | tinyint(3) unsigned | 1 | 255 | 7 | 0.0275 | | sakila | payment | payment_id | smallint | smallint(5) unsigned | 1 | 65535 | 16050 | 0.2449 | | sakila | rental | rental_id | int | int(11) | 0 | 2147483647 | 16050 | 0.0000 | | sakila | staff | staff_id | tinyint | tinyint(3) unsigned | 1 | 255 | 3 | 0.0118 | | sakila | store | store_id | tinyint | tinyint(3) unsigned | 1 | 255 | 3 | 0.0118 | +--------------+------------+--------------+-----------+-----------------------+-------------+------------+----------------+----------------------+
Bonus: free advice on increasing your AUTO_INCREMENT capacity
Make it UNSIGNED. No, really. Check your definitions now.
Nice one!!
Maybe it’s a good idea to also exclude performance_schema?
@Daniel,
thanks, updated.
Nice query, just add “ORDER BY 9 DESC” in my case.
Thx !
ORDER BY is on you 🙂