… be quite inaccurate as secondary indexes tend to take more space. So we added more detailed index statistics into our xtrabackup utility. The…, index: PRIMARY, space id: 12, root page 3 estimated statistics in dictionary: key vals: 25265338, leaf pages 497839, size pages 498304 real statistics…
Post: Statistics of InnoDB tables and indexes available in xtrabackup
September 14, 2009 By 6 Comments
Post: Google's user_statistics V2 port and changes
September 12, 2008 By 9 Comments
… USER_ and CLIENT_STATISTICS commands Our port includes: INFORMATION_SCHEMA tables – USER_STATISTICS, CLIENT_STATISTICS, INDEX_STATISTICS, TABLE_STATISTICS Access protection – USER_STATISTICS, CLIENT_STATISTICS available only for user with SUPER or PROCESS privileges. INDEX_STATISTICS, TABLE_STATISTICS shows only…
Post: Unused indexes by single query
September 12, 2008 By 14 Comments
…_schema.statistics `s` LEFT JOIN information_schema.index_statistics IST ON CONCAT_WS(‘.’, s.TABLE_SCHEMA, s.TABLE_NAME, s.INDEX_NAME) = IST.INDEX_NAME WHERE IST.INDEX_NAME IS…
Post: Dropping unused indexes
January 15, 2009 By 6 Comments
… index ‘,group_concat(index_name separator ‘,drop index ‘),’;') stmt from (SELECT DISTINCT s.TABLE_SCHEMA, s.TABLE_NAME, s.INDEX_NAME FROM information_schema.statistics s LEFT JOIN information_schema.index_statistics iz ON (s.TABLE_SCHEMA = iz.TABLE_SCHEMA AND s.TABLE_NAME=iz.TABLE_NAME AND s.INDEX_NAME=iz.INDEX…
Post: check-unused-keys: A tool to interact with INDEX_STATISTICS
June 26, 2009 By 10 Comments
With the growing adoption of Google’s User Statistics Patch**, the need for supporting scripts has become clear. To … of databases to ignore –ignore-indexes Comma-separated list of indexes to ignore db_name.tbl_name.index_name –ignore-tables Comma-separated… help FALSE hostname localhost ignore-databases No default value ignore-indexes No default value ignore-tables No default value options-file…
Post: How (not) to find unused indexes
October 16, 2009 By 2 Comments
… | Using where | +—-+————-+———+——-+—————+————+———+——+——+————-+ 1 row in set (0.00 sec) The index on query 3 had high cardinality but should not be… helpful patch in 5.0-percona called INDEX_STATISTICS that can then show you which indexes were touched and which were not. If… parse and EXPLAIN all results, then subtract the indexes that were mentioned from all indexes known. There’s an old tool…
Post: MySQL extensions for hosting
April 24, 2008 By 11 Comments
… SHOW TABLE_STATISTICS, SHOW INDEX_STATISTICS and SHOW USER_STATISTICS. The first two can interest anyone to periodically check what data or which index are….00 sec) The statistics for tables and indexes can be cleaned with the corresponding FLUSH command (e.g. FLUSH INDEX_STATISTICS). All that can…
Post: When Does InnoDB Update Table Statistics? (And When It Can Bite)
October 6, 2011 By 5 Comments
… statistics is used for JOIN optimizations and helping the MySQL optimizer choose the appropriate index for a query. If a table’s statistics or index cardinality becomes outdated, you might see… SHOW INDEX, SHOW TABLE STATUS and SHOW [FULL] TABLES (or their corresponding queries from INFORMATION_SCHEMA.TABLES and INFORMATION_SCHEMA.STATISTICS) When…
Post: InnoDB TABLE/INDEX stats
March 20, 2010 By 5 Comments
… whether statistics recalculation should be done | INNODB_INDEX_STATS is | table_name | table name in InnoDB internal style (‘database/table’) | | index_name | index name… you can estimate how big is index is, and also what is statistics per index (or at least what InnoDB thinks about statistics in index)
Post: New patches, new builds
September 5, 2008 By 24 Comments
… | BSD | | | userstats.patch | SHOW USER/TABLE/INDEX statistics | 1.0 | Google | GPL | Added INFORMATION_SCHEMA.*_STATISTICS | | show_patches.patch | SHOW PATCHES | 1.0… tables USER_STATISTICS, INDEX_STATISTICS, TABLE_STATISTICS to INFORMATION_SCHEMA mysql> select * from information_schema.table_statistics; +————————-+———–+————–+———————-+ | TABLE_NAME | ROWS_READ | ROWS_CHANGED | ROWS_CHANGED_INDEXES…
