-- Identify databases with unused space
SELECT 
    table_schema AS database_name,
    table_name,
    data_free,
    ENGINE
FROM information_schema.tables
WHERE table_schema NOT IN ('sys', 'mysql', 'performance_schema', 'information_schema', 'test')
  AND data_free > 0;

-- Calculate total storage per database
SELECT
    table_schema AS database_name,
    SUM(table_rows) AS total_rows,
    SUM(TRUNCATE(data_length / 1024 / 1024, 2)) AS data_size_mb,
    SUM(TRUNCATE(index_length / 1024 / 1024, 2)) AS index_size_mb
FROM information_schema.TABLES
GROUP BY table_schema
ORDER BY SUM(data_length) DESC, SUM(index_length) DESC;

-- Display storage usage for individual tables
SELECT
    table_schema AS database_name,
    table_name AS table_name,
    table_rows AS row_count,
    TRUNCATE(data_length / 1024 / 1024, 2) AS data_size_mb,
    TRUNCATE(index_length / 1024 / 1024, 2) AS index_size_mb
FROM information_schema.TABLES
ORDER BY data_length DESC, index_length DESC;

-- Storage analysis for specific database
SELECT
    table_schema AS database_name,
    SUM(table_rows) AS total_rows,
    SUM(TRUNCATE(data_length / 1024 / 1024, 2)) AS data_size_mb,
    SUM(TRUNCATE(index_length / 1024 / 1024, 2)) AS index_size_mb
FROM information_schema.TABLES
WHERE table_schema = 'test_db';

-- Detailed table sizes within specific database
SELECT
    table_schema AS database_name,
    table_name AS table_name,
    table_rows AS row_count,
    TRUNCATE(data_length / 1024 / 1024, 2) AS data_size_mb,
    TRUNCATE(index_length / 1024 / 1024, 2) AS index_size_mb
FROM information_schema.TABLES
WHERE table_schema = 'test_db'
ORDER BY data_length DESC, index_length DESC;

-- Size analysis for specific table
SELECT
    table_schema AS database_name,
    table_name AS table_name,
    SUM(table_rows) AS total_rows,
    SUM(TRUNCATE(data_length / 1024 / 1024, 2)) AS data_size_mb,
    SUM(TRUNCATE(index_length / 1024 / 1024, 2)) AS index_size_mb
FROM information_schema.TABLES
WHERE table_schema = 'test_db' AND table_name = 't1';

-- Detect fragmented tables across databases
SELECT 
    table_schema AS database_name,
    table_name,
    data_free,
    ENGINE
FROM information_schema.tables
WHERE table_schema NOT IN ('information_schema', 'mysql')
  AND data_free > 0
ORDER BY data_free DESC;

-- Check fragmentation for specific table
SHOW TABLE STATUS LIKE 't1';

-- Reclaim space from MyISAM tables
OPTIMIZE TABLE t1;

-- Rebuild InnoDB tables to eliminate fragmentation
ALTER TABLE t1 ENGINE = InnoDB;

-- Calculate total database size including data and indexes
SELECT 
    SUM(DATA_LENGTH) + SUM(INDEX_LENGTH) AS total_bytes
FROM information_schema.tables
WHERE table_schema = 'test_db';

-- Human-readable database size
SELECT 
    CONCAT(ROUND(SUM(DATA_LENGTH/1024/1024), 2), 'MB') AS total_size
FROM information_schema.tables
WHERE table_schema = 'test_db';

-- Size of specific table
SELECT 
    CONCAT(ROUND(SUM(DATA_LENGTH/1024/1024), 2), 'MB') AS table_size
FROM information_schema.tables
WHERE table_schema = 'test_db' AND table_name = 't1';

-- Transaction blocking analysis
SELECT
    r.trx_id AS waiting_transaction_id,
    r.trx_mysql_thread_id AS waiting_thread_id,
    r.trx_query AS waiting_query,
    b.trx_id AS blocking_transaction_id,
    b.trx_mysql_thread_id AS blocking_thread_id,
    b.trx_query AS blocking_query
FROM information_schema.innodb_lock_waits w
INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id
INNER JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id;

-- Detailed transaction wait information
SELECT
    b.trx_state,
    e.state,
    e.time,
    d.state AS blocking_process_state,
    d.time AS blocking_process_time,
    a.requesting_trx_id,
    a.requested_lock_id,
    b.trx_query,
    b.trx_mysql_thread_id,
    a.blocking_trx_id,
    a.blocking_lock_id,
    c.trx_query AS blocking_transaction_query,
    c.trx_mysql_thread_id AS blocking_transaction_thread
FROM information_schema.INNODB_LOCK_WAITS a
LEFT JOIN information_schema.INNODB_TRX b ON a.requesting_trx_id = b.trx_id
LEFT JOIN information_schema.INNODB_TRX c ON a.blocking_trx_id = c.trx_id
LEFT JOIN information_schema.PROCESSLIST d ON c.trx_mysql_thread_id = d.id
LEFT JOIN information_schema.PROCESSLIST e ON b.trx_mysql_thread_id = e.id
ORDER BY a.requesting_trx_id;

-- Identify inactive transactions (MySQL 5.6+)
SELECT
    a.trx_id,
    a.trx_state,
    a.trx_started,
    a.trx_query,
    b.ID,
    b.USER,
    b.DB,
    b.COMMAND,
    b.TIME,
    b.STATE,
    b.INFO,
    c.PROCESSLIST_USER,
    c.PROCESSLIST_HOST,
    c.PROCESSLIST_DB,
    d.SQL_TEXT
FROM information_schema.INNODB_TRX a
LEFT JOIN information_schema.PROCESSLIST b ON a.trx_mysql_thread_id = b.id AND b.COMMAND = 'Sleep'
LEFT JOIN PERFORMANCE_SCHEMA.threads c ON b.id = c.PROCESSLIST_ID
LEFT JOIN PERFORMANCE_SCHEMA.events_statements_current d ON d.THREAD_ID = c.THREAD_ID;

-- Find sleeping transactions (MySQL 5.5)
SELECT
    a.trx_id,
    a.trx_state,
    a.trx_started,
    a.trx_query,
    b.ID,
    b.USER,
    b.HOST,
    b.DB,
    b.COMMAND,
    b.TIME,
    b.STATE,
    b.INFO
FROM information_schema.INNODB_TRX a
LEFT JOIN information_schema.PROCESSLIST b ON a.trx_mysql_thread_id = b.id
WHERE b.COMMAND = 'Sleep';

-- Transactions running longer than one minute
SELECT
    trx_id,
    trx_started,
    trx_mysql_thread_id
FROM INFORMATION_SCHEMA.INNODB_TRX
WHERE trx_started < DATE_SUB(NOW(), INTERVAL 1 MINUTE)
  AND trx_operation_state IS NULL
  AND trx_query IS NULL;