… need to be executed during EXPLAIN phase yet making it unusable to check the optimizer performance. Solution for this problem was to use set optimizer_search… manual will chose best value automatically. Making this change I could bring optimization, and full query execution time to less than 50ms. Low values… clear how far from optimal plans do we get by using a greedy search. From the same discussion we can learn how automatic…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
… record to make a lookup in the PK. Both these lookups are performed in a single call to the … compared to MySQL 5.5, and this does not make sense to me. Probably its due to a bug in how … cold query runs the optimizer would switch to using plan ‘a’, which does not involve MRR, and the query time …
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 2 Comments
…query times. But I also had to turn on MRR because BKA uses the MRR interface. Note also the additional optimizer… workload Now let’s see how effective are the join optimizations when the workload fits entirely… not make much of a difference in the query times. Conclusion BKA improves the query time …
Post: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
March 12, 2012 By 12 Comments
… 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…
Post: How FLUSH TABLES WITH READ LOCK works with Innodb Tables
March 23, 2012 By 20 Comments
… and others use FLUSH TABLES WITH READ LOCK to temporary make MySQL read only. In many cases the… TABLES WITH READ LOCK does not work as optimally as you could think it works. Even though… SELECT queries you can be potentially waiting for hours for this statement to complete. Here is example how it…
Post: Diamond Keynote Panel, BOFs, Lightning Talks, and McAfee and AOL Sponsorships
April 2, 2012 By 1 Comment
…interests to enjoy some quality face time. Topics include “Query Optimization”, “…How to Design a Scalable System”, and “How to Become a Rockstar DBA”. The Percona Live MySQL Conference and Expo promises to…make it this year, keep your eyes open for recordings of the keynote sessions which we plan to…
Comment: Introducing new type of benchmark
February 25, 2012 By 16 Comments
… they’re often coded to suit how they’ll likely … to actually overhaul the optimizer to start to resolve this? It seems to…to get developers to stop using major benchmarks for their tuning, so why not make them dance to…query engines in their own ways this presents an ideal opportunity and a chance to…
Post: How adding another table to JOIN can improve performance ?
August 1, 2008 By 12 Comments
… work while I know I used exactly this trick to optimize some nasty queries. It looks like equality propagation is working here (note… have something to learn about MySQL Optimizer gotchas 
At the same time I figured out how to make MySQL Optimizer to do what we want to do – Just add yet another table to…
Post: How Percona does a MySQL Performance Audit
November 24, 2008 By 17 Comments
…can pull for server optimization. Schema and query optimization can give much greater … query plan. This is where you need to really know how to write queries and how EXPLAIN…how to interpret what we’re showing, and why the proposed modifications are better. If the client approves it, we may make…
Post: MySQL 4 to MySQL 5 Upgrade performance regressions
June 6, 2007 By 8 Comments
…not just 5-10% you often see for simple queries due to generally fatter code. The problem in both …MySQL 4.1 to 5.0 upgrade notes ? ? Furthermore if there were no good ideas how to make XA to work with …number of commits if you can. It is great optimization idea anyway. Also please do not read this …
