Optimization of MySQL table join query

The following articles mainly introduce the optimization of MySQL table join queries and the actual operations of LEFTJOIN and RIGHTJOIN. If you are interested, you can click the following articles to view them, I hope it will help you in this regard. ALEFTJOINBjoin_condition is implemented in mysql as follows: Table B depends on table A and its dependent

The following articles mainly introduce the optimization of MySQL JOIN table queries, and the actual operations of left join and right join. If you are interested, you can click the following articles to view them, I hope it will help you in this regard. A left join B join_condition is implemented in mysql as follows: Table B depends on table A and

The following articles mainly introduce the optimization of MySQL JOIN table queries, and the actual operations of left join and right join. If you are interested, you can click the following articles to view them, I hope it will help you in this regard.

A left join B join_condition is implemented in mysql as follows:

Table B depends on table A and all tables it depends on.

Table A depends on all tables in the left join condition (except B ).

The left join condition is used to determine how to read records from Table B (in other words, any condition in the WHERE clause does not work ).

All standard MySQL join table queries are optimized. Except that a table is always read after all the tables it depends on. If this is a circular dependency, MySQL will regard it as wrong.

All standard WHERE optimizations are executed.

If A record in A matches the WHERE clause, but no record in B matches the ON condition, a B record is generated, and all its field values are set to NULL.

If left join is used to search for records that do NOT exist in some tables and the WHERE clause has a condition: col_name is null, And the col_name field IS defined as not null, mySQL will stop searching after finding a record that matches the left join condition (used in combination with a specific index key.

The implementation of right join is similar to that of left join, but the role of the table is reversed.

The MySQL join Table query optimizer calculates the order of table connections. The order in which tables are read is forcibly specified by left join, and the use of STRAIGHT_JOIN can help the connection optimization program to execute faster, because there will be fewer table queuing checks. Note: If you execute the following type of query, MySQL performs a full table scan on B, because LEFT JOIN forces that you must do this before reading d:

 
 

  
  
SELECT * 
  
  
FROM a,b LEFT JOIN c ON (c.key=a.key) LEFT JOIN d ON (d.key=a.key) 
  
  
WHERE b.key=d.key; 
 
 

To solve this problem, rewrite the query as follows:

 
 

  
  
SELECT * 
  
  
FROM b,a LEFT JOIN c ON (c.key=a.key) LEFT JOIN d ON (d.key=a.key) 
  
  
WHERE b.key=d.key; 
 
 

Starting from 4.0.14, MySQL performs the following left join optimization: If the WHERE condition of the generated NULL record is always false, left join will become a normal connection.

For example, if the value of t2.column1 is NULL in the following query, the result of the WHERE clause is false:

 
 

  
  
SELECT * FROM t1 LEFT JOIN t2 ON (column1) WHERE t2.column2=5; 
 
 

Therefore, this can be safely converted into a common connection query:

 
 

  
  
SELECT * FROM t1,t2 WHERE t2.column2=5 AND t1.column1=t2.column1; 
 
 

This is faster to query, because if there is a better query plan, mysql will use T2. To forcibly specify the table sequence, use STRAIGHT_JOIN. The above content is related to the optimization of MySQL JOIN Table query: left join and right join introduction, I hope you will gain some benefits.