… problem, might be even not problem with your MySQL configuration, queries and hardware, even though fixing these does help in many… too much in this case throttling by having relatively short queries and introducing “sleeps” between them can be a good idea…
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 2 Comments
… additional optimizer switches and variables in case of MariaDB 5.5. You can read more about these variables here. The query used… desc, o_orderdate LIMIT 10; In-memory workload Now let’s see how effective are the join optimizations when the workload fits… not make much of a difference in the query times. Conclusion BKA improves the query time by a huge margin for IO…
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… best value automatically. Making this change I could bring optimization, and full query execution time to less than 50ms. Low values, such… – 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…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
…, n_name, c_address, c_comment order by revenue desc LIMIT 20; In-memory workload Now let’s see how effective… So during cold query runs the optimizer would switch to using plan ‘a’, which does not involve MRR, and the query time for… improvement in the optimizer, as it is clearly a part of the optimizer‘s job to select the best query execution plan. I…
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 quickly…
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 …. This query uses index (type: index, key: a, key_len: 103, Extra: ”): EXPLAIN SELECT * FROM table1 ORDER BY a LIMIT 1698, 1… or filesort? On mysql manual I notice that the above query satisfies all conditions required for MySQL to use an index…
Post: ORDER BY ... LIMIT Performance Optimization
September 1, 2006 By 93 Comments
… need to know about ORDER BY … LIMIT optimization to avoid these problems ORDER BY with LIMIT is most common use of ORDER BY… index then requested by LIMIT. However if you’re dealing with LIMIT query with large offset efficiency will suffer. LIMIT 1000,10 is likely to be way slower than LIMIT 0,10…
Post: MySQL Query Cache
July 27, 2006 By 69 Comments
… start of the query – Query Cache does simple optimization to check if query can be cached. As I mentioned only SELECT queries are cached – so… of the main features which limits query cache effectiveness – if you have high write application such as forums, query cache efficiency might be…
Post: Using delayed JOIN to optimize count(*) and LIMIT queries
April 6, 2007 By 22 Comments
… which need to be joined to get query result. If you’re executing count(*) queries for such result sets MySQL will perform… way MySQL generates full rows while executing queries with limit before throwing them away which makes queries with high offset values very expensive… remove JOIN for count(*) and do JOIN after limiting result set for retrieval queries. Lets look at following simple example with one…
