MySQL optimization subquery

• A subquery is used to influence the number and order of rows produced in a subquery. For example:

• SELECT *  fromT1WHERET1.column1inch  (SELECTColumn1 fromT2ORDER  bycolumn1);SELECT *  fromT1WHERET1.column1inch  (SELECT DISTINCTColumn1 fromT2);SELECT *  fromT1WHERE EXISTS  (SELECT *  fromT2 LIMIT1); The//limit keyword is not in a subquery that contains the IN keyword (replaced with exists)

• Replace and subquery do join operations. For example:

  SELECT DISTINCT  from WHERE inch (  SELECT from T2);

  Replace:

  SELECT DISTINCT  from T1, T2   WHERE = t2.column1;

• Some subqueries are rewritten as join connections in order to be compatible with older versions that do not support subqueries. However, in some cases, overwriting subqueries as join operations can improve performance;

• Remove external statements that appear in subqueries. For example:

  SELECT *  from T1   WHERE inch (SelectfromUNIONallSELECT from T2);

  Replace:

  SELECT *  from T1   WHERE inch (SelectfromOR in (select from T2);

  For another example:

  SELECT (SELECT+5 from T2;

  Replace:

  SELECT (SELECTfrom+5 from T2;

• Use a row subquery instead of a correlated subquery. For example:

  SELECT *  from T1   WHERE inch (SELECT from T2);

  Replace:

  SELECT *  from T1   WHERE EXISTS (SELECT*fromWHERE t2.column1=t1.column1                  and T2.column2=t1.column2);

• In NOT (a = ANY (...)) lieu a <> ALL (...) .

• In x = ANY (table containing (1,2)) lieu x=1 OR x=2 .

• In = ANY lieu EXISTS .

• Because unrelated subqueries typically return a single row of results IN 通常慢于 = . For example:

  SELECT *  from T1   WHERE t1. col_name = (SELECTfromWHERE= some_const);

  Replace:

  SELECT *  from T1   WHERE t1. col_name inch (SELECTfromWHERE= some_const);

• MySQL executes unrelated subqueries one time. Use explain to ensure that a subquery is truly irrelevant.

• MySQL rewrite,, IN ALL ANY and SOME subquery attempts to increase the likelihood that a select column is indexed in a subquery.

• MySQL replaces the with index lookup feature, which describes a special join (Unqie subquery or index subquery) subquery (as in the following form): Explain

  indexed_columnsingle_table...)

• MySQL added a previous expression (min () or Max ()) unless null value or an empty collection:

  value{all| any| SOME} {> | < | >= | <=} ( uncorrelated subquery )

  For example,:

  WHERE 5 > All (SELECT x from T)

• May be optimized to: WHERE 5 > (SELECT MAX (x) from T)

MySQL optimization subquery