Simple optimization tutorial for table connection query in MySQL _ MySQL

This article mainly introduces the simple optimization tutorial for table connection query in MySQL. table connection query is one of the most common basic operations in MySQL, so its optimization is also worth noting, for more information, see a left join B join_condition in MySQL. the execution process is as follows:

· Set Table B based on all tables on which table A and table A depend.

· Set Table A according to all tables (except B) used in the LEFT JOIN condition.

· The left join condition is used to determine how to search rows from Table B. (In other words, do not use any conditions in the WHERE clause ).

· All standard Joins can be optimized, except for tables read from all the tables it depends on. If a circular dependency occurs, MySQL prompts an error.

· Perform all standard WHERE optimizations.

· If A has A row that matches the WHERE clause, but B does not have A row that matches the ON condition, another B row is generated. all columns are set to NULL.

· If left join is used to locate rows that do NOT exist in some tables and perform the following test: the col_name is null in the WHERE section, WHERE col_name IS a column declared as not null, mySQL finds a row that matches the left join condition and stops searching for other rows (for a specific keyword combination.

The execution of right join is similar to that of left join, but the role of the table is the opposite.

The order in which the join Optimizer calculates the table to be joined. The forced read sequence of left join and STRAIGHT_JOIN can help the JOIN Optimizer to work faster, because fewer table exchanges are checked. Note that if the following type of query is executed, MySQL performs full scan B, because LEFT JOIN forces it to read before 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;

In this case, the reverse order of a is used for restoration, and B is listed in the FROM clause:

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;

MySQL can perform the following left join optimization: if the NULL row is generated, the WHERE condition is always false, and the left join is changed to a normal JOIN.

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

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

Therefore, you can safely convert a query to a normal join:

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

This is faster, because MySQL can use table T2. To force table order, use STRAIGHT_JOIN.

First, filter the conditions and then create indexes for related query fields in the table based on the table connection. in this way, the query speed is quite fast in the case of big data multi-table joint query, let's take a look at the statement in the example below:

SELECT M.*,SS.SensorCode,SS.SensorStatus,SS.ManufacturerId,SS.Electricity,SS.Voltage,SS.MinElectricity,SS.MinVoltage,SS.Temperature,SS.StatusUpdteDate,SS.UpdateStatus ,tp.PricingStrategyid,tps.FreeDuration,bat.BerthTypeId FROM(SELECT T.* , BS.ParkStatus,BS.ChangeTime ,CA.CantonName, SE.SectionName FROM(SELECT A.* ,B.BerthId,B.BerthCode,B.BerthAddress,B.BerthStatus,B.LineDirection,B.CantonId,B.SectionId FROM (SELECT AR.AreaId,AR.AreaCode,AR.AreaName FROM  SYS_Area AS AR WHERE 1=1  AND AR.AreaCode='110' ) A LEFT JOIN SYS_Berth AS B ON B.AreaId=A.AreaId ) TJOIN SYS_BerthStatus AS BS ON T.BerthCode=BS.BerthCode JOIN SYS_Canton AS CA ON T.CantonId=CA.CantonId JOIN SYS_Section AS SE ON T.SectionId=SE.SectionId )MLEFT JOIN SYS_Sensor SS ON M.BerthCode=SS.BerthCode LEFT JOIN TRA_PricingBerth AS tp ON tp.BerthCode=M.BerthCode  LEFT JOIN TRA_PricingStrategy AS tps ON tps.PricingStrategyId=tp.PricingStrategyId LEFT JOIN SYS_BerthAndType AS bat ON bat.BerthCode=M.BerthCode ORDER BY BerthCode ASC

The above is a simple optimization tutorial _ MySQL for table connection query in MySQL. For more information, see PHP Chinese network (www.php1.cn )!