… about the MRR optimization available in MySQL 5.6 here: http://dev.mysql.com/doc/refman/5.6/en/mrr-optimization.html and as… 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=off’ read_rnd_buffer_size=4M (only on MySQL…
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 2 Comments
… MySQL 5.6 config: optimizer_switch=’index_condition_pushdown=off’ optimizer_switch=’mrr=on’ optimizer_switch=’mrr_cost_based=off’ optimizer_…optimizer_switch=’index_condition_pushdown=off’ optimizer_switch=’mrr=on’ optimizer_switch=’mrr_sort_keys=on’ optimizer_switch=’mrr_cost_based=off’ optimizer_…
Post: Guidance for MySQL Optimizer Developers
September 20, 2009 By 4 Comments
… – make optimizer pluggable and make it possible to stick to old optimizer behavior with new MySQL Version. Make Cost Model Adjustable MySQL Optimizer looks at… or because MySQL simply does not have execution method to resolve query in optimal way – loose index scan, hash join, sort merge join…
Post: How fast can you sort data with MySQL ?
August 18, 2007 By 26 Comments
…sort merge passes are required for sort completion: mysql> show status like “sort%”; +——————-+——-+ | Variable_name | Value | +——————-+——-+ | Sort_merge_passes | 321 | | Sort_range | 0 | | Sort_rows | 10 | | Sort… or data sets) – The optimal sort_buffer_size in this …
Post: ORDER BY ... LIMIT Performance Optimization
September 1, 2006 By 93 Comments
… JOIN MySQL still will not be able to use it as Optimizer is not smart enough yet to detect such cases: mysql> explain… it is indexed) Force index if needed In some cases MySQL Optimizer may prefer to use different index, which has better selectivity… problem is ether extending your indexes so MySQL Optimizer does not have to chose between better sort or better lookup or use FORCE…
Post: Possible optimization for sort_merge and UNION ORDER BY LIMIT
September 18, 2007 By 10 Comments
… well could be retrieving sorted streams by the indexes and doing merge sort to get resulted rows in sorted order. Once this is… interesting is the fact MySQL is unable to handle even basic UNION with limit (without order by) optimally – in creates result set… possible optimizer improvement to do. P.S This post is inspired by Does MySQL Optimize UNION with LIMIT clause topic on our MySQL Forums…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
… MySQL manual, especially the option …commanual optimizer_prune_levelblogpercona.commanual optimizer_search_depthblogpercona.commanual optimizer_…sort_buffer_sizeblogpercona.commanual Sort_merge_passesblogpercona.commanual Sort_rangeblogpercona.commanual Sort_rowsblogpercona.commanual Sort…
Post: Thinking about running OPTIMIZE on your Innodb Table ? Stop!
December 9, 2010 By 13 Comments
…mysql> optimize table a; +——–+———-+———-+——————————————————————-+ | Table | Op | Msg_type | Msg_text | +——–+———-+———-+——————————————————————-+ | test.a | optimize… by sorting was …
Post: MySQL Performance - eliminating ORDER BY function
October 17, 2007 By 6 Comments
One of the first rules you would learn about MySQL Performance Optimization is to avoid using functions when comparing constants or order… means we’re sorting by constant and so sort phase can be skipped all together. Note in this case MySQL Optimizer is rather smart… functions are different MySQL is not able to do this optimization even in cases when this would be possible: mysql> explain select * from…
Post: High-Performance Click Analysis with MySQL
December 22, 2008 By 13 Comments
… with vanilla MySQL, you will need to aggregate your data. What you want to do is aggregate in ways that optimize the… joins on large data sets are very expensive. If MySQL supported sort-merge or hash joins, you’d have other possibilities…/XtraDB tables… Optimize For I/O It is pretty much inevitable: if you do this kind of data processing in MySQL, you…
