Hi the output goes like this:
| Database | Size |
|---|
| mysql | 8.36 |
| information_schema | 0.17 |
| performance_schema | 0.00 |
| wordpress | 1.13 |
Also, I have a query that goes like this:
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' AND IF (c.MAX_VALUE > 0, ROUND(100 * t.AUTO_INCREMENT / c.MAX_VALUE, 2), -1) > 50
ORDER BY
`Usage (%)` DESC;
With example output like this:
| TABLE_NAME | COLUMN_TYPE | MAX_VALUE | AUTO_INCREMENT | Usage (%) |
|---|
| glpi_alerts | int(11) | 2147483647 | 1 | 56.71 |
| glpi_apiclients | int(11) | 2147483647 | 2 | 52.21 |
| glpi_authldapreplicates | int(11) | 2147483647 | 1 | 50.01 |
and so on..
It should return the table with the biggest index usage in descending order. And I want to get at least the first three rows to display on my sensor, just TABLE_NAME and Usage will be fine. Thanks!
Add comment