• Skip to main content
  • Skip to primary sidebar

DBA Diaries

Thoughts and experiences of a DBA working with SQL Server and MySQL

When are Innodb Table Statistics Updated?

June 18, 2014 by Andy Hayes Leave a Comment

Innodb statistics are used by the query optimizer to assist it in choosing an efficient query execution plan. They are estimated values relating to each Innodb table and index.

But what updates them? Let’s take a look.

Operations that update Innodb table statistics

Typically this happens during metadata statements such as SHOW INDEX or SHOW TABLE STATUS.

It also occurs when querying INFORMATION_SCHEMA tables such as TABLES and STATISTICS.

This is default behaviour however a variable was introduced in version 5.5.4 which allowed the administrator to override this. The variable name is innodb_stats_on_metadata.

When it is turned off, Innnodb statistics are not updated during those operations. For schemas that have a large number of tables or indexes, this can have a positive effect on access speeds. It can also improve the stability of execution plans that involve Innodb tables.

If it is turned off the alternative is to run ANALYZE TABLE which is a similar operation to those mentioned involving INFORMATION_SCHEMA etc. It will update the statistics but place a read lock on the table during the process.

Related Posts:

  • sql grouping sets
    Using SQL GROUPING SETS for Multiple GROUP BY…
  • How to fix "conversion failed when converting date and/or time from character string"
    How to fix "conversion failed when converting date…

Filed Under: All Articles, MySQL Administration Tagged With: mysql, performance

About Andy Hayes

Andy Hayes is a DBA working with SQL Server since version 7.0. He has a wonderful wife and two beautiful children. He loves database technology, playing cricket, and blogging. He is passionate about sharing his experiences as a DBA and learning more to further his understanding and knowledge. You can follow me on Twitter, check out my Facebook page or follow me on Google+

Reader Interactions

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Primary Sidebar

Categories

  • All Articles (84)
  • Career Development (8)
  • MySQL Administration (18)
  • MySQL Performance (2)
  • SQL Server Administration (24)
  • SQL Server News (3)
  • SQL Server Performance (14)
  • SQL Server Security (3)
  • SQL Tips and Tricks (21)

Top 10 Popular Posts

  • Using sp_change_users_login to fix SQL Server orphaned users
  • MySQL SHOW USERS? – How to List All MySQL Users and Privileges
  • How to shrink tempdb
  • How to Transfer Logins to Another SQL Server or Instance
  • How to Delete Millions of Rows using T-SQL with Reduced Impact
  • T-SQL – How to Select Top N Rows for Each Group Using ROW_NUMBER()
  • New T-SQL features in SQL Server 2012 – OFFSET and FETCH
  • How to Kill All MySQL Processes For a Specific User
  • Using exec sp_who2 to help with SQL Server troubleshooting
  • How to fix “conversion failed when converting date and/or time from character string”

Recent Posts

  • How to fix “conversion failed when converting date and/or time from character string”
  • Using SQL GROUPING SETS for Multiple GROUP BY Queries in a Single Query
  • How to Setup MySQL Master Master Replication
  • How To Use SQL to Convert a STRING to an INT
  • How to set up MySQL Replication Tutorial

Search

Connect

  • Twitter
  • Facebook
  • RSS

About

  • Cookie Policy
  • Disclaimer
  • About
Copyright ©