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.

  • Which ENGINE?
    – Rick James
    Dec 6, 2022 at 23:23
  • @Rick James -- InnoDB
    – Marshall C
    Dec 7, 2022 at 0:50

1 Answer 1


You can update the index statistics without doing OPTIMIZE TABLE

When you run


This is what OPTIMIZE TABLE does under the hood for InnoDB


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


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

  • 2
    It's worth reassuring the user that ANALYZE TABLE does not lock the table, does not take a long time regardless of table size, and it's totally safe to run this anytime. It's a read-only task. Dec 7, 2022 at 1:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.