… making it unusable to check the optimizer performance. Solution for this problem was to use set optimizer_search_depth=0, rarely used option which as per manual will chose best value automatically. Making this change I could bring optimization… – it picks value of min(number of tables, 7) essentially limiting search depth to no more than 7 at which complexity is…
Comment: Joining many tables in MySQL - optimizer_search_depth
April 23, 2012 By 6 Comments
… 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. I… join optimizer is N!. The goal of this choice was to provide a conservative limit that guarantees that any query can be optimized…
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: Getting around optimizer limitations with an IN() list
January 9, 2010 By 9 Comments
…? Anyone out there created a zipcode database and created a “search within x numer of miles” function ? Thankful for any tips… wouldn’t scale. To understand why these sorts of geographic search queries are problematic in MySQL, it’s best to show… for this is simply a missing feature of the MySQL optimizer – and it has to do with using x BETWEEN 30…
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… limiting factors with all of them being optional. This is hard nut to crack and I know on high end custom search… slower than LIMIT 0,10. It is true most users will not go further than 10 page in results, however Search Engine…
Post: Using GROUP BY WITH ROLLUP for Reporting Performance Optimization
September 17, 2007 By 12 Comments
… logs you would look at most popular web pages or search engine keywords which bring you most of the traffic. If… some silly like query to illustrate some search is required, so count(*) can’t be optimized away for MyISAM Tables. To show… counted for group by operation. The obvious optimization is to get rid of LIMIT 10 and just fetch all groups and sum…
Post: Four ways to optimize paginated displays
September 24, 2008 By 26 Comments
… paginated display is one of the top optimization scenarios we see in the real world. Search results pages, leaderboards, and most-popular…. There are ways to optimize so you don’t have to do quite so much offsetting and limiting. I wrote about this…, the more rows you are scanning and discarding with that LIMIT. This technique works great in conjunction with the first one…
Post: MySQL 5.5.8 - in search of stability
January 3, 2011 By 11 Comments
… for the initial run. Later I will change them in searching for optimal values. innodb_file_per_table = true innodb_data_file… following the advice in article [1], we will try to limit the percentage of dirty pages and increase I/O capacity… me show the result I got running Percona Server in optimized mode: innodb_buffer_pool_size = 26G innodb_buffer_pool_instances…
Post: Using delayed JOIN to optimize count(*) and LIMIT queries
April 6, 2007 By 22 Comments
… applications you would see main (fact) table which contains search fields and dimension tables which contain more information about facts … similar way MySQL generates full rows while executing queries with limit before throwing them away which makes queries with high offset…” MySQL and remove JOIN for count(*) and do JOIN after limiting result set for retrieval queries. Lets look at following simple…
Post: Announce: Front End Performance Optimization
October 10, 2008 By 8 Comments
… quite typical you also would see external queue, message parsing, search systems etc being important components besides MySQL. MySQL is important… adding front end optimization services we are able to help to deliver great performing web sites whatever performance limiting factors are. P…
