… to look into something interesting with the index cardinality statistics reported by InnoDB tables. The cardinality varies because it’s derived from estimates… why the cardinality varies in a particular pattern. Here I’ll grab a bunch of cardinality estimates from sakila.film on MySQL 5…
Post: When EXPLAIN estimates can go wrong!
October 13, 2011 By 7 Comments
… as MySQL uses “the number of rows” estimation to pick and choose indexes and it could really be picking up a wrong index… is used to generate index cardinality estimates which are then in turn used by MySQL to pick and choose indexes and hence is irrelevant to this problem. Next, I proceeded to test using MySQL 5…
Post: When Does InnoDB Update Table Statistics? (And When It Can Bite)
October 6, 2011 By 5 Comments
… optimizations and helping the MySQL optimizer choose the appropriate index for a query. If a table’s statistics or index cardinality becomes outdated, you… instances below are documented from the MySQL and InnoDB plugin’s manual: Metadata commands like SHOW INDEX, SHOW TABLE STATUS and SHOW…
Post: MySQL Crash Recovery
July 30, 2006 By 26 Comments
… only problem as we’ll see. MySQL has angel process mysqld_safe which will restart MySQL Server in most cases. It is… unsync with internal Innodb dictionary may cause MySQL to fail with wierd error messages. In MySQL 4.1 sync_frm option was… extreme load. Innodb statistics – Unlike MyISAM Innodb does not store index cardinality in tables, instead it computes them on first table access…
Post: Troubleshooting MySQL Upgrade Performance Regressions
February 15, 2012 By Leave a Comment
…, FORCE INDEX, BIG_RESULT/SMALL_RESULT. Check whatever stats are the same (run SHOW INDEXES FROM for tables involved and check cardinality) Different stats can often cause different plans. Run ANALYZE TABLE on both MySQL Versions…
Post: ANALYZE: MyISAM vs Innodb
September 3, 2008 By 9 Comments
…index accesses) will not use index cardinality…
Post: How (not) to find unused indexes
October 16, 2009 By 2 Comments
… be able to find any indexes that have low cardinality, in an effort to find out what indexes should be removed. This… sec) The cardinality of status index is woeful, but provided that the application is always only sending query 1 to MySQL it’s… population greater than 1000. An automated search for low cardinality indexes wouldn’t have revealed it’s uselessness. For range…
Post: Multiple column index vs multiple indexes
August 22, 2008 By 13 Comments
…: Q1 will not use Index Merge technique for low cardinality table but instead pick to do single index scan. I’m not aware… index is faster for Q3 in case of low cardinality even though MySQL can’t use index well. You’re right MySQL can’t and MySQL… cases but in case of high cardinality multiple index configuration. Note4 Q5 was just run on high cardinality tables to show what difference…
Post: When the subselect runs faster
March 18, 2010 By 9 Comments
… is an index on the col1. MySQL estimated the cardinality of that index as 87, though what was of course misleading as index cardinality in this… scanning table in index order. We can just use FORCE INDEX hint to override MySQL index choice: mysql> explain select * from table FORCE INDEX(PRIMARY) where…
Comment: Do you always need index on WHERE column ?
August 28, 2007 By 16 Comments
… no matter the cardinality, index traversing speed depends on the index cardinality. Note in this case it is not pure index scan (“Using index” in explan… count(name) should be same as count(*) and so covering index could be used while MySQL is not smart enough for this.
