One of the very frequent cases with performance problems with MySQL is what they happen every so often or certain times. Investigating them we find out what the cause is some batch jobs, reports and other non response time critical activities are overloading the system causing user experience to …
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 2 Comments
… config: optimizer_switch=’index_condition_pushdown=off’ optimizer_switch=’mrr=on’ optimizer_switch=’mrr_sort_keys=on’ optimizer_switch=’mrr_cost_based=off’ optimizer_switch… desc, o_orderdate LIMIT 10; In-memory workload Now let’s see how effective are the join optimizations when the workload fits…
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… chose best value automatically. Making this change I could bring optimization, and full query execution time to less than 50ms. Low… – it picks value of min(number of tables, 7) essentially limiting search depth to no more than 7 at which complexity…
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… same way this blog post is aimed at a new optimizer enhancement Index Condition Pushdown (ICP). Its available in both MySQL…. Index Condition Pushdown Traditional B-Tree index lookups have some limitations in cases such as range scans, where index parts after…
Comment: Joining many tables in MySQL - optimizer_search_depth
April 23, 2012 By 6 Comments
… implemented the so-called “greedy optimizer“, I did some experiments to determine what is a reasonable limit for the search depth (and the total number of tables) that can be optimized in reasonable time… 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…, n_name, c_address, c_comment order by revenue desc LIMIT 20; In-memory workload Now let’s see how effective…
Comment: What does Using filesort mean in MySQL?
March 15, 2012 By 27 Comments
I looked at the “ORDER BY OPTIMIZATION” section in MySQL manual but could not find a reason …_len: 103, Extra: ”): EXPLAIN SELECT * FROM table1 ORDER BY a LIMIT 1698, 1 This one uses filesort (Extra: Using filesort): EXPLAIN SELECT * FROM table1 ORDER BY a LIMIT 1699, 1 Can someone EXPLAIN if there is a rule…
Comment: The ultimate tool for generating optimal my.cnf files for MySQL
March 14, 2012 By 34 Comments
… logic against you, you wrote a book to help people optimize their my.cnf settings, yet you were wailing against the…. YET, your book without a doubt has exactly the same limitation and short-comings. Indeed a DBA might be able to…
Comment: To SQL_CALC_FOUND_ROWS or not to SQL_CALC_FOUND_ROWS?
February 27, 2012 By 53 Comments
Here is a beautiful solution; SELECT COUNT(*) FROM (SELECT 1 FROM test WHERE some_conditions LIMIT any_no) alias_table; This results count of total rows wihtout any optimizations.
Post: Getting around optimizer limitations with an IN() list
January 9, 2010 By 9 Comments
… for this is simply a missing feature of the MySQL optimizer – and it has to do with using x BETWEEN 30…
