…in MySQL 5.6 and MariaDB 5.5. This blog post is aimed at the optimizer enhancement Multi Range Read (MRR). Its available in…column, and do a range primary key lookup as follows: SELECT non_key_column from tbl WHERE pk_column IN…
Post: Multi Column indexes vs Index Merge
September 19, 2009 By 31 Comments
… STATE columns. The better strategy is often to have combined multi-column index on (AGE,STATE). Lets see why it is the case. MySQL… to use multi column indexes. But the problems with Index Merge do not stop there. It is currently rather restricted in which conditions it… multiple column indexes. This is in case you’re using OR between the columns. In this case the combined index is useless and MySQL has…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
… MySQL manual, especially the option …collationsblogpercona.commanual Com_show_column_typesblogpercona.commanual Com_…enable_pstackblogpercona.commanual engine_condition_pushdownblogpercona.commanual error_…wait_timeoutblogpercona.commanual locked_in_memoryblogpercona.commanual logblogpercona….
Post: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
March 12, 2012 By 12 Comments
I have been working with Peter in preparation for the talk comparing the optimizer enhancements in MySQL 5.6 and MariaDB 5.5. We are taking a look at and benchmarking optimizer enhancements one by one. So in the same way this blog post is aimed at a new optimizer enhancement Index Condition Pushdown (ICP). Its available in both MySQL 5…
Post: MySQL 6.0 vs 5.1 in TPC-H queries
March 25, 2008 By 17 Comments
… ‘Using Index condition‘, what I was able to find is Baron’s bug with no mention in MySQL 6.0 manual. No mentions about Using MRR also, but I guess it is Multi-Range-Read…query, as MySQL used index to scan rows and then did access to data to read other columns. But for…
Post: 3 ways MySQL uses indexes
September 12, 2009 By 28 Comments
…index type in MySQL – BTREE can…column in explain plan to see how many index parts are actually used for row lookup. Very common problem I see is multi column…in explain which correspond to scanning (potentially) complete table in the index order. It is very important to understand in which conditions…
Post: Using Flexviews - part one, introduction to materialized views
March 23, 2011 By 3 Comments
…reading. This is the first in a multi-part series describing Flexviews. Update…columns and rows, just like a database table, but it is temporary in nature, usually existing only in… of a SQL statement. MySQL even includes a SQL … distinct or other conditions must be fully computed and stored in a …
