… Cardinality | Sub_part | Packed | Null | Index_type | Comment | +—————+————+———-+————–+————-+———–+————-+———-+——–+——+————+———+ | antest_innodb | 1 | i | 1 | i | A | 245900 | NULL | NULL | | BTREE | | | antest_innodb… with INSERT in Innodb table we do not get NULL cardinality as with MyISAM but instead we get very wrong cardinality which shows us…” key prefix cardinality is underestimated 5 times and the combined (c,i) key cardinality is overestimated 2 times. So Innodb stats are are…
Post: When EXPLAIN estimates can go wrong!
October 13, 2011 By 7 Comments
… picking up a wrong index simply because of the wrong estimate. The customer reported that he changed the value of innodb_stats_sample… rows estimation”, because page sampling is used to generate index cardinality estimates which are then in turn used by MySQL to… ’0′, KEY `id` (`id`), KEY `type_created` (`type`,`created`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci First I did…
Comment: Database problems in MySQL/PHP Applications
August 11, 2006 By 12 Comments
…an auto_increment column) in nearly (emphasize nearly) every InnoDB table can have very serious and negative …if you use many tables you’re doing something wrong it > is frequently told by people with… Indexes smartly.” Indexing columns that have very low cardinality will often be a waste of resources…
Post: MySQL Crash Recovery
July 30, 2006 By 26 Comments
… hidden and do not expose itself instantly – you may notice wrong query results days after crash. Sometimes corrupted tables may be… you have some hardware problems (99%) or found new Innodb bug (1%) Innodb recovery should be automatic and bring your database to… takes longer time on extreme load. Innodb statistics – Unlike MyISAM Innodb does not store index cardinality in tables, instead it computes them on…
Post: Troubleshooting MySQL Upgrade Performance Regressions
February 15, 2012 By Leave a Comment
…SHOW INDEXES FROM for tables involved and check cardinality) Different stats can often cause different plans…. some cases the faster plan may be wrong plan from optimizer standpoint and updating stats…. You may consider disabling innodb_stats_on_metadata and increasing innodb-stats-sample-pages to get…
Comment: Why InnoDB index cardinality varies strangely
September 28, 2009 By 5 Comments
…b (A, B), KEY ix_a_c (A, C) ) ENGINE=InnoDB Our typical queries are select S from T where …= ?; Well, here sampling comes to the play. We will get cardinalities ix_a_b (1) x1 ix_a_b (2) …means that one of the queries is always executed using wrong index!!! I talked to Percona people. In their opinion …
