1

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.

Bill Karwin
  • 14,693
  • 2
  • 30
  • 42
Marshall C
  • 131
  • 4

1 Answers1

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

RolandoMySQLDBA
  • 182,700
  • 33
  • 317
  • 520
  • 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. – Bill Karwin Dec 07 '22 at 01:47