MySQL HINT: Straight_JOIN

MySQL HINT: Straight_JOIN friends from the production environment may encounter: originally run a good query statement, after a while, it may suddenly become very bad. A major possible cause is that the data distribution has changed, resulting in a change in the MySQL optimizer's selection of the driver table, in this case, the index becomes invalid, so when you are idle and have a cup of coffee, you should collect the statistics of two more tables. At this time, Straight_JOIN debuted. MySQL only supports Nested Loop Join. for detailed usage, see the previous blog: click Open Link http://www.bkjia.com/database/201301/186885.html Compared with Oracle, it is not known that Straight_JOIN is equivalent to the following in Oracle: USE_NL, so, the principle and applicability are probably the same, but the selection of the driver table, MySQL Optimizer may not be as intelligent as Oracle, MySQL uses a simple and crude method: which table has a small result set, which table is used as the drive table? Two reasons can be used: the Straight_JOIN ① MySQL optimizer is not powerful, and the driver table ② Nested Loop Join is incorrectly selected. Applicable scenarios: ==> generally, an index is used in the connected table, and the index Selectivity is good (that is, selecti.pdf is close to 1) => that is, the record set of the driver table is relatively small (<10000), and the inner table needs to have an effective access method (Index) for general optimization operations: ① show full processlist; <=== find TOP-SQL ② explain + TOP-SQL; <=== query SQL Execution Plan note: In the EXPLAIN results, the first row of the table appears is a classic optimization example of the driver table: when the explain output contains: "Using filesort 」, even "Using temporary", we should polish our eyes, maintain the same posture as the chicken blood, keep the optimization at the moment, it will be much easier to optimize at the moment, as far as possible to ensure that the sorting field is in the driving table