… patterns the optimizer recognizes, and which are left to the developer to refactor manually. There could also be some cases where optimizing a… the index. Example: WHERE (last_name, first_name) = (‘Karwin’, ‘Bill’); Q: On the Dynamic Pivot, the straight join, can you explain more… it helped? When I did not use the STRAIGHT_JOIN, the query optimizer reordered the tables. It seemed to prefer an index…
Post: Virident vCache vs. FlashCache: Part 2
May 17, 2013 By 3 Comments
…that configuration C appeared to produce the most optimal results, I moved on to reviewing FlashCache performance… also include the numbers from the vCache test where the time-based flushing is enabled. As …max_heap_table_size = 64M sort_buffer_size = 4M join_buffer_size = 4M thread_cache_size = 1000 …
Post: Keynotes, BOFs, and the Community Networking Reception at Percona Live MySQL Conference and Expo
April 15, 2013 By 1 Comment
…& What’s New With the Project” Sergei Petrunia, Optimizer Developer, and Colin Charles, Chief Evangelist, MariaDB, for…the cloud and big data. If you can join us in Santa Clara for the Percona Live…the conference and we’ll announce when and where the keynote recordings and breakout session slide decks…
Post: A case for MariaDB's Hash Joins
May 31, 2012 By Leave a Comment
…32M optimizer_switch=’join_cache_incremental=on’ optimizer_switch=’join_cache_hashed=on’ optimizer_switch=’join_cache_bka=on’ join_cache_level=4 join_buffer_size=32M join_buffer…can see where Hash Joins work best and where they don’t. First of all Hash joins only work for equijoins. Hash join work …
Post: The MySQL optimizer, the OS cache, and sequential versus random I/O
April 28, 2008 By 17 Comments
… join order takes an hour with another, and the optimizer chose the poorer of the two join orders. Why is one join… filesort | | dim1 | eq_ref | 1 | Using where | | dim2 | eq_ref | 1 | Using where | +——-+———–+———–+———————————+ As we saw in the previous post,…
Post: How adding another table to JOIN can improve performance ?
August 1, 2008 By 12 Comments
… will stop using this optimization method if there are too many combinations to check). Instead you could use JOIN to get list…: PRIMARY key_len: 3 ref: NULL rows: 30 Extra: Using where; Using index *************************** 2. row *************************** id: 1 select_type: SIMPLE table…
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 4 Comments
…optimizer_switch=’mrr_cost_based=off’ optimizer_switch=’join_cache_incremental=on’ optimizer_switch=’join_cache_hashed=on’ optimizer_switch=’join_cache_bka=on’ join_cache_level=8 join… customer, orders, lineitem FORCE INDEX (i_l_orderkey) where c_mktsegment = ‘AUTOMOBILE’ and c_custkey = o_custkey …
Post: ORDER BY ... LIMIT Performance Optimization
September 1, 2006 By 100 Comments
… field from the table which is not first in the join order index can’t be used. Sometimes it means breaking… can be optimized even if it is done by second table: mysql> explain select test.i from test, test t where test… as Optimizer is not smart enough yet to detect such cases: mysql> explain select test.i from test, test t where test…
Post: Using delayed JOIN to optimize count(*) and LIMIT queries
April 6, 2007 By 22 Comments
… fact where i select count(*) from fact left join dim on val=id where i select i,pad from fact left join dim on val=id where i select i,pad from ( select i,val from fact where i
Post: How to find MySQL queries worth optimizing ?
September 11, 2012 By 6 Comments
…; select * from sbtest where pad=’abc’; Rows_examined=0 same as Rows_sent meaning this query is optimized quite well. Note you… in set (0.00 sec) In this case we actually join 2 tables but because the access type to the tables…: 12F23 SET timestamp=1347398892; select * from sbtest a,sbtest b where a.k=2 and b.id=a.id; +—-+————-+——-+——–+—————+———+———+————-+——+——-+ | id | select…
