…adoption of Google’s User Statistics Patch**, the need for supporting scripts has become … a nicer interface than directly querying the INFORMATION_SCHEMA database. check-unused-keys can be invoked … ignore –ignore-indexes Comma-separated list of indexes to ignore db_name.tbl_name.index_name –ignore…
Comment: check-unused-keys: A tool to interact with INDEX_STATISTICS
June 27, 2009 By 10 Comments
@petitchevalroux No, it does not. It queries the INFORMATION_SCHEMA.INDEX_STATISTICS table, which is only available with the Google patch set.
Post: Using Multiple Key Caches for MyISAM Scalability
November 26, 2008 By 8 Comments
… information_schema.table_statistics where table_schema=’test’ and table_name in (‘a’,'b’); +————–+————+———–+————–+————————+ | TABLE_SCHEMA | TABLE_NAME | ROWS_READ | ROWS_CHANGED | ROWS_CHANGED_X_INDEXES…) from information_schema.table_statistics where table_schema=”test” and t.table_name in (“a”,”b”)))/2*4000000000)),”;”) cmd from information_schema.table_statistics ts join information_schema.tables…
Post: New patches, new builds
September 5, 2008 By 24 Comments
… Next, we added 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…
Post: MySQL 5.6 vs MySQL 5.5 and the Star Schema Benchmark
March 11, 2013 By 7 Comments
…, index_name, count(*) pages, sum(is_old=’YES’) old, count(*) – sum(is_old=’YES’) hot, sum(number_records) records from information_schema.innodb… pool and the other secondary indexes have been pushed out of the buffer pool. mysql> select * from information_schema.innodb_buffer_pool_stats\G….000000 | 0.000000 | 0 | 0 | 0 | 0 | 29 | 29 | | 9 | statistics | 0.000130 | 0.000097 | 0.000000 | 0.000000 | 0 | 0…
Post: When Does InnoDB Update Table Statistics? (And When It Can Bite)
October 6, 2011 By 7 Comments
… commands like SHOW INDEX, SHOW TABLE STATUS and SHOW [FULL] TABLES (or their corresponding queries from INFORMATION_SCHEMA.TABLES and INFORMATION_SCHEMA.STATISTICS) When 1 / 16th of the table or 2Billion rows has been modified, whichever comes first. ./row/row0mysql.c:row_update_statistics…
Post: How expensive is USER_STATISTICS?
June 2, 2012 By 14 Comments
… whether it’s safe to enable the so-called USER_STATISTICS features of Percona Server in a heavy-use production server… some new INFORMATION_SCHEMA tables that add counters for activity on users, hosts, tables, indexes, and more. This is very helpful information. It lets… used less than 10MB of memory to store all the statistics. It might be time for us to consider making this…
Post: InnoDB TABLE/INDEX stats
March 20, 2010 By 7 Comments
… mentioned two additional INFORMATION_SCHEMA tables available in XtraDB: It is INNODB_TABLE_STATS INNODB_INDEX_STATS These table show statistics about InnoDB tables… 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: How (not) to find unused indexes
October 16, 2009 By 2 Comments
… an INFORMATION_SCHEMA query to be able to find any indexes that have low cardinality, in an effort to find out what indexes should… helpful patch in 5.0-percona called INDEX_STATISTICS that can then show you which indexes were touched and which were not. If… alternative is to either use a proxy that checks EXPLAIN information (like QUAN) or set your slow query log to zero…
Post: Estimating Replication Capacity
July 20, 2010 By 4 Comments
…normally – if you need to add/remove indexes and do other schema changes you probably would be looking at …running=1 and run following query: mysql> select * from information_schema.user_statistics where user=”#mysql_system#” \G *************************** 1. row *************************** USER: …
