… EMPTY_QUERIES: 70748 and INDEX/TABLE statistics: mysql> select * from information_schema.index_STATISTICS limit 10; +————–+————+—————–+———–+ | TABLE_SCHEMA | TABLE_NAME | INDEX_NAME | ROWS_READ | +————–+————+—————–+———–+ | art119…
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… 10 rows in set (0.00 sec) mysql> SHOW INDEX_STATISTICS LIKE ‘hosting2%’; +——————————————–+———–+ | Index | Rows_read | +——————————————–+———–+ | hosting2.wp_options.option_name…
Post: When Does InnoDB Update Table Statistics? (And When It Can Bite)
October 6, 2011 By 7 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… the tables’ statistics. Luckily, this can be controlled with Percona Server with the variable innodb_stats_auto_update (from MySQL Community 5…
Post: Dropping unused indexes
January 15, 2009 By 6 Comments
… mysql> select concat(‘alter table ‘,d.table_schema,’.',d.table_name,’ drop 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…
Post: Multi Column indexes vs Index Merge
September 19, 2009 By 31 Comments
… multi-column index on (AGE,STATE). Lets see why it is the case. MySQL indexes are (with few exceptions) BTREE indexes – this index type is… there is no correlation statistics available. mysql [localhost] {msandbox} (test) > explain select avg(length(val)) from idxtest ignore index(i2) where i1=50… it is right plan considering the statistics MySQL had available. It is very easy to disable index merge if you do not want…
Post: MySQL Binaries Percona build10
December 11, 2008 By 12 Comments
…/mysql/slow_query.log | for compatibility with MySQL 5.1 and | userstat_running | OFF | to control gathering of user statistics. With…_scan_* | | userstatsv2.patch | SHOW USER/TABLE/INDEX statistics | V2 | Google | GPL | Added INFORMATION_SCHEMA.*_STATISTICS | | show_patches.patch | SHOW PATCHES | 1….
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 …> The target MySQL server host –ignore-databases Comma-separated list of databases to ignore –ignore-indexes Comma-separated list of indexes to ignore db_name.tbl_name.index_name –ignore-tables Comma-separated…
Post: MySQL Upgrade Webinar Questions Followup
June 28, 2012 By 7 Comments
… development since MySQL 4.1, so in-place upgrade of MySQL 5.0 to MySQL 5.5 … using Percona Server with enabled expand_fast_index_creation feature, as I just wrote this … statistics (ANALYZE TABLE) or disabling some of the optimization/statistics related features in new version. Innodb statistics …
Post: MySQL 5.6.10 Optimizer Limitations: Index Condition Pushdown
March 14, 2013 By 2 Comments
… of one of the nicest features of the newer MySQL optimizer: the Index Condition Pushdown Optimization, or ICP, which we have previously… you need more manual care and tuning now. MySQL is conservative about “Using index” -in most cases it will be the right… I would like to see better algorithms, statistics computation and monitoring variables about index usage in the future, now that we have…
Post: Percona Server 5.1.55-12.6
March 7, 2011 By 5 Comments
… – The ROWS_READ columns of TABLE_STATISTICS and INDEX_STATISTICS were not properly updated when a query involved index lookups on an InnoDB table…, on servers with huge buffer pools, adding or removing an index even on an empty InnoDB table could take a long…
