MySQL5.6 optimized the dispatch Query

Netizen Huang Hua Liang released an SQL Execution Plan

650) this. width = 650; "src =" http://www.bkjia.com/uploads/allimg/140212/22261W124-0.jpg "title =" 5.5.jpg "alt =" wkiol1l50mwdh3pu%7wzstzm0173.jpg "/>

Q: The field TOP_TITLE_ID in table t is the primary key. Why does order by still have Using temporary; Using filesort?

Later, I asked for the table and tested it on my machine. If I changed it to the following SQL statement, there would be no Using temporary; Using filesort

650) this. width = 650; "src =" http://www.bkjia.com/uploads/allimg/140212/22261Q151-1.jpg "title =" 5.5_1.jpg "alt =" wKiom1L50h-iYgV1AAN1s01J8DI458.jpg "/>

So I tested it on 5.6 and found that:

650) this. width = 650; "src =" http://www.bkjia.com/uploads/allimg/140212/22261S146-2.jpg "title =" 5.6.jpg "alt =" wKiom1L50oThf_doAAUcH1PMmyc418.jpg "/>

For the same SQL statement, Using temporary; Using filesort is removed from MySQL5.6.

Carefully read the manual,

650) this. width = 650; "src =" http://www.bkjia.com/uploads/allimg/140212/22261RW5-3.jpg "style =" float: none; "title =" 5.6_1.jpg "alt =" wKiom1L504PysREXAAD0IMIxVzs550.jpg "/>

650) this. width = 650; "src =" http://www.bkjia.com/uploads/allimg/140212/22261Q218-4.jpg "style =" float: none; "title =" 5.6_2.jpg "alt =" wKioL1L501_jtwOZAAFHPAggZSQ654.jpg "/>

That is to say, an index is used to store the subquery results into a temporary table and the temporary table is used to execute a connection. In table c, the TOP_TITLE_ID field is the index, and the TOP_TITLE_ID field associated with table t is the primary key, and Using temporary; Using filesort disappears.

This article is from the "hechun's technical column" blog, please be sure to keep this source http://hcymysql.blog.51cto.com/5223301/1358049