… more than 7 tables in join I think faster execution plan computation would be more important than backward compatibility. In MySQL 5.6 things are likely to get even better handling joins of many tables as optimizer heuristics are improved…
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 2 Comments
… the optimizer enhancements in MySQL 5.6 and MariaDB 5.5. This blog post is targeted at the join related optimizations introduced in the optimizer. These optimizations… obviously when the first operand used in the build step is such that the hash table fits in memory. You can read more…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
… talk comparing the optimizer enhancements in MySQL 5.6 and MariaDB 5.5. This blog post is aimed at the optimizer enhancement Multi Range… the MRR optimization available in MySQL 5.6 here: http://dev.mysql.com/doc/refman/5.6/en/mrr-optimization.html and as available in MariaDB… + 1 MRR range scans had to be performed. As in the table above you can with default buffer size of 256K, MariaDB…
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: MariaDB 5.3 is released as GA!
March 5, 2012 By 17 Comments
….3 and MySQL 5.6 optimizer improvements, and both of them look very good indeed. Peter will present the results in an “optimizer standoff… queries through speedier joins, faster subqueries, and elimination of useless tables in joins Thread pooling and a segmented key cache for improved…
Post: How FLUSH TABLES WITH READ LOCK works with Innodb Tables
March 23, 2012 By 20 Comments
… Xtrabackup, MyLVMBackup and others use FLUSH TABLES WITH READ LOCK to temporary make MySQL read only. In many cases the period for which… the problem is. As of MySQL 5.5 FLUSH TABLES WITH READ LOCK does not work as optimally as you could think it… not actively changing users, stored procedures etc (which are stored in MyISAM tables anyway) you can consider using –no-lock option with…
Comment: What does Using filesort mean in MySQL?
March 15, 2012 By 27 Comments
… “ORDER BY OPTIMIZATION” section in MySQL manual but could not find a reason why MySQL does not use index in the following scenario: CREATE TABLE table1… notice that the above query satisfies all conditions required for MySQL to use an index but it does not. I just… can check whether MySQL can use indexes to resolve the query. It cannot if you see Using filesort in the Extra column…
Comment: Joining many tables in MySQL - optimizer_search_depth
April 23, 2012 By 6 Comments
… for the search depth (and the total number of tables) that can be optimized in reasonable time. I reached the conclusion that if…! = 5040, while 8! = 40320. The worst-case complexity of MySQL‘s join optimizer is N!. The goal of this choice was to… investigate the potential pitfalls with the change to plan pruning in MySQL 5.6. Ideally, the server should analyze the query and…
Comment: Finding out largest tables on MySQL Server
April 24, 2012 By 24 Comments
… a large discrepancy in the sizes, is this an indicator that it may be a good opportunity to run OPTIMIZE and thus reclaim some hard disk space? Taking real-world data, I have for example a table that looks… disk, that table‘s .ibd file looks like this: # cd /var/lib/mysql/databasename # ls -lh tablename* -rw-rw—- 1 mysql mysql 8.7K…
Post: Can we get faster expression handling in MySQL
March 30, 2010 By 7 Comments
… benchmarks comparing arithmetic expression handling in Sphinx to one in MySQL. The result ? Sphinx scored …% comparable (as we can see in the table results are even different) and some …. Many other companies looking to optimize MySQL problem, especially in analytics space when expressions should …
