… is one additional join optimization in MariaDB that I would like to quickly explain here, and that is Hash Joins. Hash Join As I…-memory workload Now let’s see how effective are the join optimizations when the workload fits entirely in memory. For the purpose… Hash Join and the Key-ordered Scan are disabled. IO bound workload Now let’s see how effective are the join optimizations when…
Post: Joining many tables in MySQL - optimizer_search_depth
April 20, 2012 By 6 Comments
… making it unusable to check the optimizer performance. Solution for this problem was to use set optimizer_search_depth=0, rarely used… hypothesis that most users have joins with few tables, – it is not clear how far from optimal plans do we get by… things are likely to get even better handling joins of many tables as optimizer heuristics are improved so much higher search depths…
Comment: Joining many tables in MySQL - optimizer_search_depth
April 23, 2012 By 6 Comments
…? At the time when I implemented the so-called “greedy optimizer“, I did some experiments to determine what is a reasonable… depth (and the total number of tables) that can be optimized in reasonable time. I reached the conclusion that if we… join optimizer is N!. The goal of this choice was to provide a conservative limit that guarantees that any query can be optimized…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
… were made in the MySQL config: optimizer_switch=’index_condition_pushdown=off’ optimizer_switch=’mrr=on’ optimizer_switch=’mrr_sort_keys=on’ (only on MariaDB 5.5) optimizer_switch=’mrr_cost_based… lookups as well. But this works only with joins and specifically with Block Access Join Algorithms. So I am not going to…
Post: MariaDB 5.3 is released as GA!
March 5, 2012 By 17 Comments
… very good indeed. Peter will present the results in an “optimizer standoff” talk at the upcoming MySQL conference, and Timour and… log enabled Faster queries through speedier joins, faster subqueries, and elimination of useless tables in joins Thread pooling and a segmented key… offers microsecond timestamp support, as well as a lot of optimizer improvements. Even the standard MySQL 5.5 offers pluggable authentication…
Comment: Introducing new type of benchmark
February 25, 2012 By 16 Comments
… be used. Your tpcc-mysql for example doesn’t use JOINs, something we know is expensive in the MySQL world, yet Oracle and to some extent SQL Server handle better. JOIN has long been something MySQL has done badly, people have… before we’ve seen MySQL look to actually overhaul the optimizer to start to resolve this? It seems to me the…
Post: Diamond Keynote Panel, BOFs, Lightning Talks, and McAfee and AOL Sponsorships
April 2, 2012 By 1 Comment
… pleased to introduce two new sponsors including McAfee which recently joined as a Diamond Sponsor and AOL which recently agreed to… interests to enjoy some quality face time. Topics include “Query Optimization”, “MySQL and Ruby on Rails”, “MySQL in the Cloud”, and…
Post: How FLUSH TABLES WITH READ LOCK works with Innodb Tables
March 23, 2012 By 20 Comments
….5 FLUSH TABLES WITH READ LOCK does not work as optimally as you could think it works. Even though with general… WITH READ LOCK is waiting for that very nasty “full join” select to complete. What is worse as the statement started…
Post: The MySQL optimizer, the OS cache, and sequential versus random I/O
April 28, 2008 By 17 Comments
… one join order takes an hour with another, and the optimizer chose the poorer of the two join orders. Why is one join order…. Let’s start with the MySQL query optimizer. The optimizer tries to choose the best join order based on its cost metric; it… know why one join order is faster than the other. But why didn’t the optimizer know this, too? The optimizer does know…
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… optimize some nasty queries. It looks like equality propagation is working here (note the number of rows for second table in join… to make MySQL Optimizer to do what we want to do – Just add yet another table to the join so the info…
