When MySQLTuner reports fragmentation but mysqlcheck --optimize -A doesn't resolve all issues, we need a more surgical approach. Here's how to pinpoint problematic tables:

-- Check fragmentation status for specific tables
SELECT 
    table_schema,
    table_name,
    data_length,
    index_length,
    data_free,
    (data_free / (data_length + index_length)) * 100 AS fragmentation_pct
FROM 
    information_schema.tables
WHERE 
    table_schema NOT IN ('information_schema', 'mysql', 'performance_schema')
    AND data_free > 0
ORDER BY 
    fragmentation_pct DESC
LIMIT 19;

The mysqlcheck utility sometimes skips tables that are actively in use. For stubborn fragmentation cases:

-- Manual optimization for specific tables
OPTIMIZE TABLE important_db.customer_orders;
OPTIMIZE TABLE logging_db.event_history;

For InnoDB tables specifically, consider:

-- Alternative approach for InnoDB
ALTER TABLE large_table ENGINE=InnoDB;

Create a stored procedure to regularly check and log fragmentation:

DELIMITER //
CREATE PROCEDURE monitor_fragmentation()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE db_name VARCHAR(64);
    DECLARE tbl_name VARCHAR(64);
    DECLARE frag_pct DECIMAL(5,2);
    
    DECLARE cur CURSOR FOR
        SELECT table_schema, table_name,
        (data_free/(data_length+index_length))*100
        FROM information_schema.tables
        WHERE engine IN ('InnoDB','MyISAM')
        AND table_schema NOT IN ('mysql','information_schema','performance_schema')
        AND (data_free/(data_length+index_length))*100 > 10;
    
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    OPEN cur;
    read_loop: LOOP
        FETCH cur INTO db_name, tbl_name, frag_pct;
        IF done THEN
            LEAVE read_loop;
        END IF;
        
        INSERT INTO fragmentation_log 
        (database_name, table_name, fragmentation_pct, check_date)
        VALUES (db_name, tbl_name, frag_pct, NOW());
        
        IF frag_pct > 30 THEN
            SET @sql = CONCAT('OPTIMIZE TABLE ', db_name, '.', tbl_name);
            PREPARE stmt FROM @sql;
            EXECUTE stmt;
            DEALLOCATE PREPARE stmt;
        END IF;
    END LOOP;
    CLOSE cur;
END //
DELIMITER ;

For frequently fragmented large tables, consider partitioning:

ALTER TABLE historical_data
PARTITION BY RANGE (YEAR(record_date)) (
    PARTITION p2020 VALUES LESS THAN (2021),
    PARTITION p2021 VALUES LESS THAN (2022),
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION pmax VALUES LESS THAN MAXVALUE
);

When native MySQL tools aren't sufficient:

• pt-online-schema-change from Percona Toolkit
• gh-ost for GitHub's online schema migrations
• Percona XtraBackup for physical optimizations

Table fragmentation occurs when data becomes scattered across the storage system, leading to inefficient disk usage and slower query performance. While MySQLTuner can detect fragmentation, it doesn't always provide specific table names or detailed repair instructions.

To get a precise list of fragmented tables, run this SQL query:

SELECT 
    TABLE_SCHEMA as 'Database',
    TABLE_NAME as 'Table',
    DATA_LENGTH as 'Data Size',
    INDEX_LENGTH as 'Index Size',
    DATA_FREE as 'Free Space'
FROM 
    information_schema.TABLES 
WHERE 
    DATA_FREE > 0
ORDER BY 
    DATA_FREE DESC;

This query shows tables with unused space (DATA_FREE), which indicates fragmentation. Tables with significant DATA_FREE values relative to their DATA_LENGTH are prime candidates for optimization.

While mysqlcheck --optimize -A is convenient, it might not always work effectively. Here are alternative approaches:

-- Method 1: OPTIMIZE TABLE (best for MyISAM)
OPTIMIZE TABLE database_name.table_name;

-- Method 2: ALTER TABLE (works for InnoDB)
ALTER TABLE database_name.table_name ENGINE=InnoDB;

-- Method 3: Dump and reload (most thorough)
mysqldump database_name table_name > table_dump.sql
mysql database_name < table_dump.sql

Create a stored procedure to regularly check for fragmentation:

DELIMITER //
CREATE PROCEDURE check_fragmentation(IN threshold BIGINT)
BEGIN
    SELECT 
        TABLE_SCHEMA,
        TABLE_NAME,
        DATA_LENGTH,
        INDEX_LENGTH,
        DATA_FREE,
        ROUND((DATA_FREE/(DATA_LENGTH+INDEX_LENGTH+DATA_FREE))*100,2) AS frag_percent
    FROM 
        information_schema.TABLES
    WHERE 
        DATA_FREE > threshold
        AND TABLE_SCHEMA NOT IN ('information_schema','mysql','performance_schema')
    ORDER BY 
        frag_percent DESC;
END //
DELIMITER ;

-- Usage example:
CALL check_fragmentation(1048576); -- 1MB threshold

For InnoDB tables, fragmentation behaves differently. Consider these approaches:

-- Check InnoDB fragmentation status
SHOW STATUS LIKE 'Innodb_page_size';
SHOW STATUS LIKE 'Innodb_buffer_pool_pages%';

-- Rebuild the clustered index (primary key)
ALTER TABLE table_name FORCE;

-- Adjust innodb_file_per_table and innodb_file_format
SET GLOBAL innodb_file_per_table=ON;
SET GLOBAL innodb_file_format=Barracuda;

Implement these strategies to minimize future fragmentation:

• Schedule regular OPTIMIZE TABLE operations during low-traffic periods
• Use proper data types to avoid unnecessary row updates
• Implement partitioning for large tables
• Configure appropriate InnoDB settings in my.cnf