0

Let's create a new index on an existing table:

CREATE INDEX `IDX_some_index`
    ON `some_table` (`a_column`, `another_column`);

Is this useful?

ANALYZE TABLE `some_table`;

Can anyone point to some documentation clarifying whether the CREATE INDEX process analyze the target table or not?

jeremycole
  • 187
  • 5
analogue
  • 101
  • 3

1 Answers1

3

CREATE INDEX does not issue ANALYZE TABLE.

When it comes to InnoDB, running ANALYZE TABLE is kind of useless. Why ?

I wrote this post From where does the MySQL Query Optimizer read index statistics? 2.5 years ago.

In it, I explained that InnoDB can overwrite statistics by means of just running queries. The Query Optimizer will do depth searches in BTREE indexes and take guesses at the cardinality of index column combinations. In a relatively short time, I would say that index stats can get stale. This applies to older versions of MySQL 5.

From the ANALYZE TABLE Documentation for MySQL 5.6, InnoDB's stats are persistent by default. Thus, running ANALYZE TABLE on InnoDB tables for MySQL 5.6 actually means statistics are computed and will only deteriorate via heavy INSERTs, UPDATEs, and DELETEs.

As for you question, I say : CREATE INDEX does not issue ANALYZE TABLE automatically.

Community
  • 1
RolandoMySQLDBA
  • 182,700
  • 33
  • 317
  • 520
  • 1
    I'm familiar with a few use cases where it is clearly beneficial to run an ANALYZE TABLE once or twice per day, in that without it the query execution plans go terribly wrong (as in "why would it ever think this might be a good plan?"). With it, they are correct. – Shlomi Noach Jan 16 '14 at 05:43