Large deletes, churn-heavy updates, and repeated bulk loads can leave a MySQL or MariaDB table with stale optimizer statistics or poorly packed data pages. Refreshing the metadata first, then rebuilding only the tables that still need it, keeps query planning predictable without turning routine maintenance into unnecessary table-copy work.
Two maintenance statements matter here. ANALYZE TABLE refreshes index distribution and other statistics the optimizer uses for join order and access-path choices, while OPTIMIZE TABLE rebuilds the table or runs the engine-specific equivalent to reclaim unused space and tighten page layout. The SQL is the same on both servers, but the engine behavior is not identical: current MySQL 8.4 maps InnoDB optimization to ALTER TABLE … FORCE with online DDL semantics, while current MariaDB 11.4 still reports a recreate-plus-analyze path for the same statement.
These operations still deserve a maintenance window on large or hot tables. ANALYZE TABLE can change execution plans immediately, OPTIMIZE TABLE needs I/O and temporary space, and DATA_FREE is only a clue. On MySQL InnoDB, DATA_FREE belongs to the tablespace that owns the table, so a successful rebuild can still leave that number non-zero.
Steps to optimize table structures in MySQL or MariaDB:
- Refresh optimizer statistics on the target table before deciding whether a physical rebuild is still justified.
$ mysql --table -u root -p -e "ANALYZE TABLE appdb.order_items;" +-------------------+---------+----------+----------+ | Table | Op | Msg_type | Msg_text | +-------------------+---------+----------+----------+ | appdb.order_items | analyze | status | OK | +-------------------+---------+----------+----------+
Current MySQL 8.4 still takes a read lock for InnoDB during ANALYZE TABLE, while current MariaDB documents InnoDB analysis as allowing reads and writes. Either way, avoid running it blindly against the busiest tables in the middle of peak traffic.
- Check the table's current size and free-space estimate after the statistics refresh.
$ mysql --table -u root -p -e "SELECT table_schema, table_name, engine, table_rows, ROUND((data_length + index_length) / 1024 / 1024, 2) AS size_mb, ROUND(data_free / 1024 / 1024, 2) AS free_mb FROM information_schema.tables WHERE table_schema = 'appdb' AND table_name = 'order_items';" +--------------+-------------+--------+------------+---------+---------+ | table_schema | table_name | engine | table_rows | size_mb | free_mb | +--------------+-------------+--------+------------+---------+---------+ | appdb | order_items | InnoDB | 3171 | 1.58 | 4.00 | +--------------+-------------+--------+------------+---------+---------+
On MySQL, run SET SESSION information_schema_stats_expiry = 0 first when a one-off INFORMATION_SCHEMA.TABLES query must bypass cached metadata. On MariaDB, this session variable does not exist.
- Inspect SHOW TABLE STATUS for the target table before rebuilding it.
$ mysql -u root -p -e "SHOW TABLE STATUS FROM appdb LIKE 'order_items'\G" *************************** 1. row *************************** Name: order_items Engine: InnoDB Version: 10 Row_format: Dynamic Rows: 3296 Avg_row_length: 482 Data_length: 1589248 Max_data_length: 0 Index_length: 114688 Data_free: 4194304 Auto_increment: 8192 Create_time: 2026-04-09 22:12:09 Update_time: 2026-04-09 22:12:09 Check_time: NULL Collation: utf8mb4_uca1400_ai_ci Checksum: NULL Create_options: Comment: Max_index_length: 0 Temporary: NCompare Rows, Data_length, Index_length, and Data_free before and after the rebuild. On MySQL InnoDB, Data_free reflects free space in the owning tablespace, so it can stay non-zero even when the rebuild succeeds.
- Verify a representative access path before and after maintenance so a statistics refresh or rebuild does not quietly push a critical query onto a worse plan.
$ mysql -u root -p -e "EXPLAIN SELECT * FROM appdb.order_items WHERE order_id = 42\G" *************************** 1. row *************************** id: 1 select_type: SIMPLE table: order_items type: ref possible_keys: idx_order_items_order_id key: idx_order_items_order_id key_len: 4 ref: const rows: 2 Extra:For runtime measurements instead of estimates, current MySQL uses EXPLAIN ANALYZE and current MariaDB uses ANALYZE SELECT, but plain EXPLAIN is the safest shared baseline.
- Run OPTIMIZE TABLE only when reclaiming space or rewriting sparsely filled pages is still part of the fix after the statistics refresh.
$ mysql --table -u root -p -e "OPTIMIZE TABLE appdb.order_items;" +-------------------+----------+----------+-------------------------------------------------------------------+ | Table | Op | Msg_type | Msg_text | +-------------------+----------+----------+-------------------------------------------------------------------+ | appdb.order_items | optimize | note | Table does not support optimize, doing recreate + analyze instead | | appdb.order_items | optimize | status | OK | +-------------------+----------+----------+-------------------------------------------------------------------+
Current MySQL 8.4 maps InnoDB optimization to ALTER TABLE … FORCE and usually keeps concurrent DML downtime low with online DDL, but it still takes brief exclusive lock phases and can fall back to table-copy behavior for cases such as FULLTEXT indexes. Current MariaDB also reports a recreate-plus-analyze path for InnoDB, and supports WAIT n or NOWAIT when failing fast on metadata-lock contention is preferable.
- Check the active session from another connection if the rebuild runs long enough to inspect.
$ mysql --table -u root -p -e "SHOW FULL PROCESSLIST;"
Look for the session whose Info shows the OPTIMIZE TABLE statement, then watch Time and State for movement. On current MySQL releases, SQL filtering belongs on performance_schema.processlist because the INFORMATION_SCHEMA.PROCESSLIST implementation is deprecated.
- Re-check table status after the rebuild and compare it with the pre-rebuild snapshot.
$ mysql -u root -p -e "SHOW TABLE STATUS FROM appdb LIKE 'order_items'\G" *************************** 1. row *************************** Name: order_items Engine: InnoDB Version: 10 Row_format: Dynamic Rows: 3294 Avg_row_length: 482 Data_length: 1589248 Max_data_length: 0 Index_length: 65536 Data_free: 0 Auto_increment: 8192 Create_time: 2026-04-09 22:11:55 Update_time: NULL Check_time: NULL Collation: utf8mb4_uca1400_ai_ci Checksum: NULL Create_options: Comment: Max_index_length: 0 Temporary: NThe clearest success signal is a clean status OK result plus post-maintenance metadata that matches the expected layout on that server. On MariaDB InnoDB with file-per-table, Data_free often drops after the recreate step; on MySQL InnoDB it may still reflect tablespace-level free space.
Mohd Shakir Zakaria is a cloud architect with deep roots in software development and open-source advocacy. Certified in AWS, Red Hat, VMware, ITIL, and Linux, he specializes in designing and managing robust cloud and on-premises infrastructures.
