How to optimize table structures in MySQL or MariaDB

Keeping table maintenance in check helps MySQL and MariaDB avoid performance surprises after bulk loads, large purges, or heavy update churn. Fresh statistics improve plan choices, and reclaiming wasted space reduces unnecessary reads that quietly inflate latency over time.

The query optimizer estimates cost using table and index statistics, so running ANALYZE TABLE after major data changes can correct row-count estimates and restore expected index usage. When a table becomes sparse or bloated, OPTIMIZE TABLE rebuilds the table and indexes (or runs an engine-specific equivalent), improving locality while reclaiming free space inside the tablespace.

Maintenance operations can be I/O intensive and can create blocking metadata locks depending on storage engine, table size, and server version. Prefer ANALYZE TABLE for routine statistics refresh, schedule OPTIMIZE TABLE during low-traffic windows, and ensure backups plus temporary disk headroom before starting rebuilds on large tables.

1. Identify candidate tables that are large or show unusually high data_free.

   mysql> SELECT
       table_schema,
       table_name,
       engine,
       table_rows,
       ROUND((data_length + index_length) / 1024 / 1024, 1) AS size_mb,
       ROUND(data_free / 1024 / 1024, 1) AS free_mb
     FROM information_schema.tables
     WHERE table_schema NOT IN ('mysql','information_schema','performance_schema','sys')
     ORDER BY data_free DESC, (data_length + index_length) DESC
     LIMIT 10;
   +--------------+------------+--------+-----------+---------+---------+
   | table_schema | table_name | engine | table_rows | size_mb | free_mb |
   +--------------+------------+--------+-----------+---------+---------+
   | app          | audit_log  | InnoDB | 8421931   | 488.1   | 214.7   |
   | app          | orders     | InnoDB | 1204880   | 512.4   | 96.0    |
   +--------------+------------+--------+-----------+---------+---------+
   2 rows in set (0.03 sec)

   data_free is an estimate; for InnoDB it is most useful for spotting tables with unusually large reclaimable space, and values can vary based on tablespace layout.

2. Capture a before snapshot for a target table to compare after maintenance.

   mysql> SELECT
       table_schema,
       table_name,
       engine,
       table_rows,
       ROUND((data_length + index_length) / 1024 / 1024, 1) AS size_mb,
       ROUND(data_free / 1024 / 1024, 1) AS free_mb
     FROM information_schema.tables
     WHERE table_schema = 'app' AND table_name = 'audit_log';
   +--------------+------------+--------+-----------+---------+---------+
   | table_schema | table_name | engine | table_rows | size_mb | free_mb |
   +--------------+------------+--------+-----------+---------+---------+
   | app          | audit_log  | InnoDB | 8421931   | 488.1   | 214.7   |
   +--------------+------------+--------+-----------+---------+---------+
   1 row in set (0.00 sec)

3. Refresh optimizer statistics for frequently queried tables with significant data churn.

   mysql> ANALYZE TABLE app.orders, app.order_items, app.audit_log;
   +----------------+---------+----------+----------+
   | Table          | Op      | Msg_type | Msg_text |
   +----------------+---------+----------+----------+
   | app.orders     | analyze | status   | OK       |
   | app.order_items| analyze | status   | OK       |
   | app.audit_log  | analyze | status   | OK       |
   +----------------+---------+----------+----------+
   3 rows in set (0.18 sec)

   ANALYZE TABLE can change execution plans by updating statistics, so validate performance on key queries after the refresh.

4. Verify a representative query plan after the statistics refresh.

   mysql> EXPLAIN SELECT * FROM app.orders WHERE customer_id = 42\G
   *************************** 1. row ***************************
              id: 1
     select_type: SIMPLE
           table: orders
            type: ref
   possible_keys: idx_customer_id
             key: idx_customer_id
         key_len: 8
             ref: const
            rows: 128
           Extra: Using where
   1 row in set (0.00 sec)

5. Rebuild a table with OPTIMIZE TABLE when reclaiming space is required.

   mysql> OPTIMIZE TABLE app.audit_log;
   +--------------+----------+----------+-------------------------------------+
   | Table        | Op       | Msg_type | Msg_text                            |
   +--------------+----------+----------+-------------------------------------+
   | app.audit_log| optimize | note     | Table does not support optimize.    |
   | app.audit_log| optimize | note     | Recreate + analyze table performed  |
   | app.audit_log| optimize | status   | OK                                  |
   +--------------+----------+----------+-------------------------------------+
   3 rows in set (12.83 sec)

   OPTIMIZE TABLE can take a metadata lock, trigger heavy I/O, and require significant temporary disk space, so schedule rebuilds during a maintenance window on large or hot tables.

6. Monitor a long-running rebuild from a separate session to confirm forward progress.

   mysql> SHOW FULL PROCESSLIST;
   +----+------+-----------+------+---------+------+----------------+------------------------------+
   | Id | User | Host      | db   | Command | Time | State          | Info                         |
   +----+------+-----------+------+---------+------+----------------+------------------------------+
   | 21 | app  | 10.0.0.12 | app  | Query   |   18 | altering table | OPTIMIZE TABLE app.audit_log |
   | 45 | root | localhost | NULL | Query   |    0 | starting       | SHOW FULL PROCESSLIST        |
   +----+------+-----------+------+---------+------+----------------+------------------------------+
   2 rows in set (0.00 sec)

   States like altering table or copy to tmp table typically indicate an active rebuild, and a steadily increasing Time value is expected for large tables.

7. Confirm table sizing after the rebuild to validate reclaimed space.

   mysql> SELECT
       table_schema,
       table_name,
       engine,
       table_rows,
       ROUND((data_length + index_length) / 1024 / 1024, 1) AS size_mb,
       ROUND(data_free / 1024 / 1024, 1) AS free_mb
     FROM information_schema.tables
     WHERE table_schema = 'app' AND table_name = 'audit_log';
   +--------------+------------+--------+-----------+---------+---------+
   | table_schema | table_name | engine | table_rows | size_mb | free_mb |
   +--------------+------------+--------+-----------+---------+---------+
   | app          | audit_log  | InnoDB | 8421931   | 312.9   | 0.0     |
   +--------------+------------+--------+-----------+---------+---------+
   1 row in set (0.00 sec)