5

I'm interested in this for InnoDB mostly, but also for MyISAM.

I looked around in information_schema and did not see this data anywhere.

I'm using MySQL 5.5.16.

Improve this question

2 Answers 2

Reset to default
3

There is no immediate control data to tell you that, but there are a few mechanisms you can setup.

MECHANISM #1

If you have binary logging enabled, simply do a grep -i "analyze table" against all the binary logs using the output from mysqlbinlog.

MECHANISM #2

If you have the general log enabled, simply do a grep -i "analyze table" against the general log file and locate the timestamp just about the command.

MECHANISM #3

You should schedule a cronjob that runs ANALYZE TABLE against all tables that have high-write, high-update, high-delete volume. That way, there is no guess work.

MECHANISM #4

Try setting innodb_stats_on_metadata to have a measure of predictability as to when an InnoDB table needs ANALYZE TABLE. (See my Mar 26, 2012 post : When are InnoDB table index statistics updated? )

CAVEAT

In the past, I have often stated that running ANALYZE TABLE table against InnoDB is useless.

Hopefully, MECHANISM #4 is probably what you need.

Improve this answer
8
  • Thanks for the detailed response. I set sql_log_bin=0 when I run analyze to prevent it from blocking other replicated statements, so I can't do #1. And I don't typically use the general log, so #2 is out. I already run analyze on a scheduled basis a la #3, I'm just looking for a way to avoid running it if it's been run recently. Commented Jan 4, 2013 at 20:05
  • As for #4, I disable innodb_stats_on_metadata in order to improve the performance of information_schema. Commented Jan 4, 2013 at 20:05
  • My conjecture: The table information_schema.STATISTICS could possibly be more stabilized by ` innodb_stats_on_metadata`. Commented Jan 4, 2013 at 21:00
  • Yeah, I'm just not willing to take the performance hit of using innodb_stats_on_metadata. I'll accept your answer now as it's clear that there is no easy way to get this data form MySQL. Commented Jan 4, 2013 at 22:22
  • 2
    I just ran an ANALYZE TABLE on an InnoDB table and it boosted performance tremendously, so I disagree with you - definitely not useless in all cases. Commented Mar 5, 2015 at 17:46
0

In case of InnoDB there are two tables that store persistent statistics details, including the timestamp of the last update (in the column last_update).

  1. mysql.innodb_table_stats
  2. mysql.innodb_index_stats

This is described in the section "InnoDB Persistent Statistics Tables" in the documentation.

Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.