Occasionally Need to OPTIMIZE TABLE after table creation -- MariaDB 10.6.11

Question

I transfer tables from one server to another by running the CREATE TABLE statement without any indexes. Then I batch insert all of the data into the table. Then I run an ALTER TABLE to add the indexes.

Occasionally when I do this I notice queries will run slowly so I run an EXPLAIN and the query is not using the correct index or not using an index at all. Then I run an OPTIMIZE TABLE, and that seems to resolve the issue.

So I have two questions :

1. Is there an automated way I can know when I need to run OPTIMIZE TABLE on a table that has become 'unoptimized'?
2. More importantly, what am I doing that is causing this issue?

I have only noticed this issue on MariaDB 10.6.11 running on Ubuntu 22.04. I have never had this issue in the past on older versions of Ubuntu/MariaDB.

This has happened previously to tables that are well over 100GB, and I just assumed it had something to do with too many updates, inserts, deletes. However, it happened this morning on a table that is only 250MB immediately after a fresh CREATE, INSERT, ALTER.

Answer 1

You can update the index statistics without doing OPTIMIZE TABLE

When you run

OPTIMIZE TABLE mytable;

This is what OPTIMIZE TABLE does under the hood for InnoDB

ALTER TABLE mytable ENGINE=InnoDB;
ANALYZE TABLE mytable;

In details

• The first line copies the table to a temp table, switching temp table for the original table.
• The second line reads the index info and compiles the index statistics

You can skip the first line and just run

ANALYZE TABLE mytable;

I discussed this before in my post from Feb 27, 2013: What is the difference between OPTIMIZE TABLE and ANALYZE TABLE table in MySQL