… Opened_table_definitionsblogpercona.commanual Opened_tablesblogpercona.commanual optimizer_prune_levelblogpercona.commanual optimizer_search_depthblogpercona.commanual optimizer_switchblogpercona.commanual partitionblogpercona.commanual performance_schemablogpercona.commanual…
Post: MySQL optimizer: ANALYZE TABLE and Waiting for table flush
February 27, 2013 By 3 Comments
The MySQL optimizer makes the decision of what execution plan to use based … problem of ANALYZE. Other commands like FLUSH TABLES, ALTER, RENAME, OPTIMIZE or REPAIR can cause threads to wait on “Waiting for…”. Conclusion Before running an ANALYZE table or any other command listed before, check the running queries. If the table that you…
Post: MySQL: Followup on UNION for query optimization, Query profiling
August 14, 2006 By 14 Comments
… different types of ranges in MySQL. IN range allows to optimize lookups on the second key part, while BETWEEN and other… retrieval which ma become very slow if IN lists are large. Take 3 IN lists 1000 values each, on appropriate 3 keyparts… exactly happens during query execution – very helpful for MySQL Performance optimization: mysql> flush status; Query OK, 0 rows affected (0.00…
Post: Joining many tables in MySQL - optimizer_search_depth
April 20, 2012 By 8 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… found the following explanation from Timour Katchaounov in MySQL mailing list archives I have some recollection that there were few main…
Post: Possible optimization for sort_merge and UNION ORDER BY LIMIT
September 18, 2007 By 10 Comments
… by. Ranges as well as IN lists make this optimization impossible, not even speaking about index merge optimization. Lets look at this example…. Once this is implemented similar approach could be used for optimizing ORDER BY with IN. This original query (in memory data… to handle even basic UNION with limit (without order by) optimally – in creates result set for the union fully and when…
Post: ORDER BY ... LIMIT Performance Optimization
September 1, 2006 By 100 Comments
… different as there are multiple category_id values in the list now so index on (category_id, date_created) can’t… is indexed) Force index if needed In some cases MySQL Optimizer may prefer to use different index, which has better selectivity… for this problem is ether extending your indexes so MySQL Optimizer does not have to chose between better sort or better…
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… so you may end up with a very big IN list. The workaround to this, is to create steppings of the…
Post: Database access Optimization in Web Applications.
September 12, 2006 By 10 Comments
This is pretty simple approach I often use called to optimize web application performance if problem happens with few pages. If… remember avoiding accessing database is the best way you can optimize database access. This applies to anything else – if you can…. It can be mild as SELECT * FROM tbl instead of listing columns you actually need or it can be as bad…
Post: System size and Performance Optimization
February 27, 2007 By 2 Comments
… ending story, you can virtually always find something else to optimize but while on generic system, not tuned by expert you… is worth to spend time and money implementing things as optimal as possible. If you’re using 50 5000$ database servers…, typically there are many alternatives and we typically can offer list of items which can be done to the system together…
Post: Optimizing repeated subexpressions in MySQL
January 23, 2009 By 11 Comments
How smart is the MySQL optimizer? If it sees an expression repeated many times, does it … like the optimizer was computing the SUM() 18 times in the first query. (A response on the internals mailing list agreed with…
