I have been working with Peter in preparation for the talk comparing the optimizer enhancements in MySQL 5.6 and MariaDB 5.5… in the same way this blog post is aimed at a new optimizer enhancement Index Condition Pushdown (ICP). Its available in both MySQL 5… limitations in cases such as range scans, where index parts after the part on which range condition is applied cannot be used for…
Post: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
March 12, 2012 By 12 Comments
Post: Multi Column indexes vs Index Merge
September 19, 2009 By 30 Comments
… you do not want it to run, however I do not know of the hint in MySQL which would allow forcing using index merge when MySQL does not think it should be used. I hope hint would be…
Post: Using index for ORDER BY vs restricting number of rows.
February 16, 2007 By 7 Comments
… Extra: Using where 1 row in set (0.00 sec) As you can see if given no hint MySQL will prefer to use index on (cat… is not as in this case MySQL needs to sort a lot of rows to display only few. If we force index as in second… badly selective using index scan is much better idea. Until MySQL is able to handle this you will have to use force index hint. The other…
Post: Troubleshooting MySQL Upgrade Performance Regressions
February 15, 2012 By Leave a Comment
…using hints such as STRAIGHT_JOIN, FORCE INDEX, BIG_RESULT/SMALL_RESULT. Check whatever stats are the same (run SHOW INDEXES… plan changes which are not seen in explain. It …not using debug binary or differently optimized binaries. When you’re about to file a bug or report the problem through MySQL…
Post: Recovering Innodb table Corruption
July 4, 2008 By 19 Comments
… simple one in this article – when page in clustered key index is corrupted. It is worse compared to having data corrupted in secondary indexes, in which… in this example. Another hint – you may want to CHECK your MyISAM table you use for recovery after MySQL crashes to make sure indexes are not…
Post: Multiple column index vs multiple indexes
August 22, 2008 By 13 Comments
… will not use Index Merge technique for low cardinality table but instead pick to do single index scan. I’m not aware of the optimizer hint… 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 does not – Full table scan is performed and in this case…
Post: Getting around optimizer limitations with an IN() list
January 9, 2010 By 9 Comments
…in MySQL, it’s best to show some execution plans on dummy data: EXPLAIN SELECT * FROM coordinates FORCE INDEX…in memory, but the original query would have full table scanned if I didn’t use a FORCE INDEX hint…
Post: MySQL Session variables and Hints
December 28, 2006 By 2 Comments
MySQL has two ways to find tune execution of particular query. First is MySQL Hints, such as SQL_BIG_RESULT, STRAIGHT_JOIN, FORCE INDEX…after executing the query. I noticed in production hints are used much more frequently than setting … to the server but it is not that critical as it is complicated …
Comment: How to find wrong indexing with glance view
August 23, 2008 By 27 Comments
…index then mysql most probably use index on “available” column only using ref access and after that filter by age. This is optimal choice. So in…mysql can use index to filter records before fetching any rows. How much it will give? Not… little and give mysql a hint by using “gender IN (‘male’,'female…
Post: Blob Storage in Innodb
February 9, 2010 By 13 Comments
…not use external blob storage pages. For example 7KB blob can be stored on the page. However if row does not…index pages is not enabled. If you’re interested to learn more about Innodb row format check out this page in…
