.. _innodb_stats: ===================== |InnoDB| Statistics ===================== This feature provides new startup options (control method and collection of index statistics estimation) and information schema views to confirm the statistics. System Variables ================ Four new system variables were introduced by this feature. .. variable:: innodb_stats_method :cli: YES :configfile: YES :scope: GLOBAL :dyn: YES :type: STRING :default: ``nulls_equal`` :allowed: ``nulls_equal``, ``nulls_unequal``, ``nulls_ignored`` The values and meanings are almost same to ``myisam_stats_method`` option of native |MySQL| (``nulls_equal``, ``nulls_unequal``, ``nulls_ignored``). But |InnoDB| doesn't have several patterns of statistics currently. Even though this option can be changed dynamically, statistics needs to be re-calculated to change the method for the table. (reference: `MyISAM Index Statistics Collection `_) **Note:** Beginning in release 5.1.56-12.7, a variable with the same and functionality was implemented in the upstream |InnoDB|. .. variable:: innodb_stats_auto_update :type: BOOLEAN :default: 1 |InnoDB| updates the each index statistics automatically (many updates were done, some information_schema is accessed, table monitor, etc.). Setting this option 0 can stop these automatic recalculation of the statistics except for "first open" and "ANALYZE TABLE command". .. variable:: innodb_stats_update_need_lock :type: BOOLEAN :default: 1 If you meet contention of ``&dict_operation_lock``, setting 0 reduces the contention. But 0 disables to update ``Data_free:`` of ``SHOW TABLE STATUS``. .. variable:: innodb_use_sys_stats_table :version: 5.1.49-11.3 Variable introduced :type: BOOLEAN :default: 0 If this option is enabled, |XtraDB| uses the ``SYS_STATS`` system table to store statistics of table indexes. Also, when |InnoDB| opens a table for the first time, it loads the statistics from ``SYS_STATS`` instead of sampling index pages. If you use a high ``stats_sample_pages`` value, the first open of a table is expensive. In such a case, this option will help. Intended behavior is to never update statistics unless an explicit ``ANALYZE TABLE`` is issued. INFORMATION_SCHEMA Tables ========================= .. table:: INFORMATION_SCHEMA.INNODB_SYS_STATS :column INDEX_ID: Index ID :column KEY_COLS: Number of Key Columns :column DIFF_VALS: Number of Different Values. :column NON_NULL_VALS: Number of Non NULL Values. .. table:: INFORMATION_SCHEMA.INNODB_SYS_TABLES Shows the information about InnoDB tables :column SCHEMA: Schema (database) name :column NAME: Table name :column ID: Table ID :column N_COLS: Number of Columns :column TYPE: :column MIX_ID: This value is obsolete, value is always 0 :column MIX_LEN: Contains 0 for regular tables and 1 for temporary tables :column CLUSTER_NAME: This value isn't supported anymore, value is always NULL :column SPACE: Tablespace ID .. table:: INFORMATION_SCHEMA.INNODB_SYS_INDEXES Shows the information about InnoDB indexes :column TABLE_ID: Table ID :column ID: Index ID :column NAME: Index Name :column N_FIELDS: Number of fields :column TYPE: :column SPACE: Tablespace ID :column PAGE_NO: The page offset within its tablespace .. table:: INFORMATION_SCHEMA.INNODB_TABLE_STATS Shows table statistics information of dictionary cached. :column table_schema: Database name of the table. :column table_name: Table name. :column rows: estimated number of all rows. :column clust_size: cluster index (table/primary key) size in number of pages. :column other_size: Other index (non primary key) size in number of pages. :column modified: Internal counter to judge whether statistics recalculation should be done. If the value of modified column exceeds "rows / 16" or 2000000000, the statistics recalculation is done when ``innodb_stats_auto_update == 1``. We can estimate the oldness of the statistics by this value. .. table:: INFORMATION_SCHEMA.INNODB_INDEX_STATS Shows index statistics information of dictionary cached. :column table_schema: Database name of the table. :column table_name: Table name. :column index_name: Index name. :column fields: How many fields the index key has. (it is internal structure of |InnoDB|, it may be larger than the ``CREATE TABLE``). :column rows_per_keys: Estimate rows per 1 key value. ([1 column value], [2 columns value], [3 columns value], ...). :column index_size: Number of index pages. :column index_pages: Number of leaf pages. Example ======= ``[innodb_stats_method = nulls_equal (default behavior of InnoDB)]`` :: mysql> explain SELECT COUNT(*), 0 FROM orgs2 orgs LEFT JOIN sa_opportunities2 sa_opportunities ON orgs.org_id=sa_opportunities.org_id LEFT JOIN contacts2 contacts ON orgs.org_id=contacts.org_id; +----+-------------+------------------+-------+-----------------+-----------------+---------+-------------------+-------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------------+-------+-----------------+-----------------+---------+-------------------+-------+-------------+ | 1 | SIMPLE | orgs | index | NULL | orgs$org_id | 4 | NULL | 128 | Using index | | 1 | SIMPLE | sa_opportunities | ref | sa_opp$org_id | sa_opp$org_id | 5 | test2.orgs.org_id | 5751 | Using index | | 1 | SIMPLE | contacts | ref | contacts$org_id | contacts$org_id | 5 | test2.orgs.org_id | 23756 | Using index | +----+-------------+------------------+-------+-----------------+-----------------+---------+-------------------+-------+-------------+ 3 rows in set (0.00 sec) ``[innodb_stats_method = nulls_unequal or nulls_ignored]`` :: mysql> explain SELECT COUNT(*), 0 FROM orgs2 orgs LEFT JOIN sa_opportunities2 sa_opportunities ON orgs.org_id=sa_opportunities.org_id LEFT JOIN contacts2 contacts ON orgs.org_id=contacts.org_id; +----+-------------+------------------+-------+-----------------+-----------------+---------+-------------------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------------+-------+-----------------+-----------------+---------+-------------------+------+-------------+ | 1 | SIMPLE | orgs | index | NULL | orgs$org_id | 4 | NULL | 128 | Using index | | 1 | SIMPLE | sa_opportunities | ref | sa_opp$org_id | sa_opp$org_id | 5 | test2.orgs.org_id | 1 | Using index | | 1 | SIMPLE | contacts | ref | contacts$org_id | contacts$org_id | 5 | test2.orgs.org_id | 1 | Using index | +----+-------------+------------------+-------+-----------------+-----------------+---------+-------------------+------+-------------+ 3 rows in set (0.00 sec) mysql> select * from information_schema.innodb_table_stats; +------------------------+-------+------------+------------+----------+ | table_name | rows | clust_size | other_size | modified | +------------------------+-------+------------+------------+----------+ | test/sa_opportunities2 | 11175 | 21 | 11 | 0 | | test/orgs2 | 128 | 1 | 0 | 0 | | test/contacts2 | 47021 | 97 | 97 | 0 | +------------------------+-------+------------+------------+----------+ 3 rows in set (0.00 sec) mysql> select * from information_schema.innodb_index_stats; +------------------------+-----------------+--------+--------------+------------+------------+ | table_name | index_name | fields | row_per_keys | index_size | leaf_pages | +------------------------+-----------------+--------+--------------+------------+------------+ | test/sa_opportunities2 | GEN_CLUST_INDEX | 1 | 1 | 21 | 20 | | test/sa_opportunities2 | sa_opp$org_id | 2 | 338, 1 | 11| 10 | | test/orgs2 | orgs$org_id | 1 | 1 | 1 | 1 | | test/contacts2 | GEN_CLUST_INDEX | 1 | 1 | 97 | 80 | | test/contacts2 | contacts$org_id | 2 | 516, 0 | 97 | 37 | +------------------------+-----------------+--------+--------------+------------+------------+ 5 rows in set (0.00 sec) Other reading ============= * `InnoDB Table/Index stats `_