MariaDB reset table index value?

I was wondering what to do when for example your table is reaching it’s maximum index values, I was using this Query to determine the index usage per table:

SELECT 
  c.TABLE_NAME,
  c.COLUMN_TYPE,
  c.MAX_VALUE,
  t.AUTO_INCREMENT,
  IF (c.MAX_VALUE > 0, ROUND(100 * t.AUTO_INCREMENT / c.MAX_VALUE, 2), -1) AS "Usage (%)" 
FROM 
  (SELECT 
     TABLE_SCHEMA,
     TABLE_NAME,
     COLUMN_TYPE,
     CASE 
        WHEN COLUMN_TYPE LIKE 'tinyint(1)' THEN 127
        WHEN COLUMN_TYPE LIKE 'tinyint(1) unsigned' THEN 255
        WHEN COLUMN_TYPE LIKE 'smallint(%)' THEN 32767
        WHEN COLUMN_TYPE LIKE 'smallint(%) unsigned' THEN 65535
        WHEN COLUMN_TYPE LIKE 'mediumint(%)' THEN 8388607
        WHEN COLUMN_TYPE LIKE 'mediumint(%) unsigned' THEN 16777215
        WHEN COLUMN_TYPE LIKE 'int(%)' THEN 2147483647
        WHEN COLUMN_TYPE LIKE 'int(%) unsigned' THEN 4294967295
        WHEN COLUMN_TYPE LIKE 'bigint(%)' THEN 9223372036854775807
        WHEN COLUMN_TYPE LIKE 'bigint(%) unsigned' THEN 0
        ELSE 0
     END AS "MAX_VALUE" 
   FROM 
     INFORMATION_SCHEMA.COLUMNS
     WHERE EXTRA LIKE '%auto_increment%'
   ) c

   JOIN INFORMATION_SCHEMA.TABLES t ON (t.TABLE_SCHEMA = c.TABLE_SCHEMA AND t.TABLE_NAME = c.TABLE_NAME)

WHERE
 c.TABLE_SCHEMA = 'Database_Name'
ORDER BY
 `Usage (%)` DESC;

Which would return something like this:

+------------------------+-------------+------------+----------------+-----------+
| TABLE_NAME             | COLUMN_TYPE | MAX_VALUE  | AUTO_INCREMENT | Usage (%) |
+------------------------+-------------+------------+----------------+-----------+
| app_crontasks          | int(11)     | 2147483647 |        1536304 |      0.07 |
| app_alerts             | int(11)     | 2147483647 |              1 |      0.00 |
| app_apiclients         | int(11)     | 2147483647 |              2 |      0.00 |
| app_replicates         | int(11)     | 2147483647 |              1 |      0.00 |
| ...                    | ...         | ...        | ...            | ...       |
+------------------------+-------------+------------+----------------+-----------+

In case it fills up to, for example, 75% then we would need to do a clean up of the database? How am I able to safely do that? Would it affect the foreign keys assigned?

On a related note: There are several ways that auto_inc ids can be “burned”. For example, INSERT IGNORE will allocate as many ids as it could need, but waste any that it fails to use. Ditto for IODKU. REPLACE (which is mostly obviated by IODKU) is effectively DELETE + INSERT, hence always burns id(s).

Whereas changing the datatype to a bigger INT will keep you going, it may be better to worry about the “burning” of ids. That is, find and fix the code.

Normalization tends to burn often. Here’s an approach to avoid wasting ids: http://mysql.rjweb.org/doc.php/staging_table#normalization