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 :
- Is there an automated way I can know when I need to run
OPTIMIZE TABLEon a table that has become 'unoptimized'?
- 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