…before MySQL has only supported one join algorithm and that is Nested Loop Join. MariaDB has introduced a new join algorithm Hash Join. This join … as revenue, o_orderdate, o_shippriority from customer, orders, lineitem FORCE INDEX (i_l_orderkey) where c_mktsegment = ‘AUTOMOBILE’ and c_custkey…
Post: Troubleshooting MySQL Upgrade Performance Regressions
February 15, 2012 By Leave a Comment
… it without side load. Setting up 2 MySQL Servers side by side (for example with MySQL Sandbox) can especially be helpful. Once… hints such as STRAIGHT_JOIN, FORCE INDEX, BIG_RESULT/SMALL_RESULT. Check whatever stats are the same (run SHOW INDEXES FROM for tables involved and check cardinality) Different stats can often cause different plans. Run ANALYZE TABLE on both MySQL…
Post: MySQL Session variables and Hints
December 28, 2006 By 2 Comments
MySQL has two ways to find tune execution of particular query. First is MySQL Hints, such as SQL_BIG_RESULT, STRAIGHT_JOIN, FORCE INDEX etc. You place these directly into the query to change how query is executed for example SELECT STRAIGHT_JOIN * FROM A FORCE INDEX…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
… the amazing MySQL manual, especially the option and variable …commanual innodb_flush_methodblogpercona.commanual innodb_force_recoveryblogpercona.commanual Innodb_have_atomic_…insert_idblogpercona.commanual interactive_timeoutblogpercona.commanual join_buffer_sizeblogpercona.commanual keep_files_…
Post: ORDER BY ... LIMIT Performance Optimization
September 1, 2006 By 93 Comments
… field from the table which is not first in the join order index can’t be used. Sometimes it means breaking normalization… (as long as it is indexed) Force index if needed In some cases MySQL Optimizer may prefer to use different index, which has better selectivity… indexes so MySQL Optimizer does not have to chose between better sort or better lookup or use FORCE INDEX to force it to use appropriate index…
Post: High-Performance Click Analysis with MySQL
December 22, 2008 By 13 Comments
…-loop joins on large data sets are very expensive. If MySQL supported sort-merge or hash joins, you… can we improve here? Especially assuming that there are indexes other than the primary key, we can shrink… beware of falling into the trap of brute-forcing a solution that really needs to be solved…
Post: The MySQL optimizer, the OS cache, and sequential versus random I/O
April 28, 2008 By 17 Comments
… do index lookups in the two dimension tables. MySQL doesn’t want to choose this join order, so we’ll force it with STRAIGHT_JOIN: explain select STRAIGHT_JOIN …. +——-+———–+———–+———————————+ | table | type…
Post: Be careful when joining on CONCAT
October 16, 2007 By 10 Comments
… – indexed varchar(100) and tb2.id – int(11) column. No matter what I did – forced it to use key, forced a different join order, it did not want to use tb1.vid index for it. And… number of rows analyzed was really huge: mysql> EXPLAIN -> SELECT -> tb1.* -> FROM tb2 -> STRAIGHT_JOIN tb1 -> WHERE -> ( -> tb1.vid LIKE ‘prefix-%’ AND…
Comment: ORDER BY ... LIMIT Performance Optimization
November 17, 2009 By 93 Comments
…| 22771 | NULL | NULL | | BTREE | | +——-+————+——————+————–+————-+———–+————-+———-+——–+——+————+———+ mysql> explain SELECT posts.* FROM posts force index (idx_post_date) INNER JOIN follow ON follow.followuserid = posts…
Post: Guidance for MySQL Optimizer Developers
September 20, 2009 By 4 Comments
… or because MySQL simply does not have execution method to resolve query in optimal way – loose index scan, hash join, sort merge join are all the examples of such. For me it is most important to ensure MySQL has… simply hints. Any way MySQL could possibly execute query should be possible to force with hints. In MySQL 4.0 this was the…
