… key constraint which implied an implicit secondary index with the same name. Fixed by detecting … MariaDB for the upstream bug #63144. CREATE TABLE or CREATE TABLE IF NOT EXISTS statements on …slave was disconnected from the master, under certain conditions, upon reconnect, it would report that it …
Post: Percona Server for MySQL 5.5.30-30.2 now available
April 10, 2013 By 10 Comments
… key constraint which implied an implicit secondary index with the same name. Fixed by detecting … MariaDB for the upstream bug #63144. CREATE TABLE or CREATE TABLE IF NOT EXISTS statements on …slave was disconnected from the master, under certain conditions, upon reconnect, it would report that it …
Post: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
March 12, 2012 By 12 Comments
… this blog post is aimed at a new optimizer enhancement Index Condition Pushdown (ICP). Its available in both MySQL 5.6 and… aimed at. Index Condition Pushdown Traditional B-Tree index lookups have some limitations in cases such as range scans, where index parts after the part on which range condition is…
Post: MySQL 5.6.10 Optimizer Limitations: Index Condition Pushdown
March 14, 2013 By 2 Comments
… of the nicest features of the newer MySQL optimizer: the Index Condition Pushdown Optimization, or ICP, which we have previously discussed on… = 1 and note like ‘%Jaime%’; On a table like this: CREATE TABLE `cast_info` ( `id` int(11) NOT NULL AUTO_INCREMENT… whole database (data and indexes fit completely in memory). Also, I was testing, as I said before, index condition pushdown. Let’s have…
Post: A case for MariaDB's Hash Joins
May 31, 2012 By Leave a Comment
… query_cache_size=0 query_cache_type=0 optimizer_switch=’index_condition_pushdown=on’ optimizer_switch=’mrr=on’ optimizer_switch=’mrr_cost… query_cache_size=0 query_cache_type=0 optimizer_switch=’index_condition_pushdown=on’ optimizer_switch=’mrr=on’ optimizer_switch=’mrr_sort… `i_s_nationkey` (`s_nationkey`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 CREATE TABLE `lineitem` ( `l_orderkey` int(11) NOT NULL DEFAULT ’0…
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 4 Comments
… will operate is that first a hash table will be created based on the columns of table t1 that are two… changes were made on MySQL 5.6 config: optimizer_switch=’index_condition_pushdown=off’ optimizer_switch=’mrr=on’ optimizer_switch=’mrr_cost… changes were made on MariaDB 5.5 config: optimizer_switch=’index_condition_pushdown=off’ optimizer_switch=’mrr=on’ optimizer_switch=’mrr_sort…
Post: MySQL Indexing Best Practices: Webinar Questions Followup
August 16, 2012 By 4 Comments
… a difference though. Q: Would you recommend creating an index in every table you create? Example: CREATE TABLE user_competition_entry user_id (INT… other index, also you only can have equality comparison as a search condition WHERE A=5 ORDER BY B will use index (A,B) for sorting optimization, for more complicated conditions you will…
Post: MySQL 5.5 and MySQL 5.6 default variable values differences
February 21, 2013 By 8 Comments
… value by default which is good change as trying to create Innodb table but getting MyISAM because Innodb was disabled for… VARIABLE_VALUE: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
… be depend on the number of rows that match the condition ‘key_column=x’. You can see that there are a… following changes were made in the MySQL config: optimizer_switch=’index_condition_pushdown=off’ optimizer_switch=’mrr=on’ optimizer_switch=’mrr_sort….5 w/ mrr_buffer_size=4M Created_tmp_disk_tables 1 1 1 1 1 Created_tmp_tables 1 1 1 1…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
…create_eventblogpercona.commanual Com_create_functionblogpercona.commanual Com_create_indexblogpercona.commanual Com_create_procedureblogpercona.commanual Com_create_serverblogpercona.commanual Com_create_tableblogpercona.commanual Com_create….commanual engine_condition_pushdownblogpercona.commanual …
