A simple optimization tutorial on table join query in Mysql _mysql

In MySQL, A left JOIN B join_condition is executed as follows:

· Set Table B based on all tables that are dependent on tables A and a.

· Set Table A for all tables (except B) that are used in the left join condition.

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

· All standard joins can be optimized, except for tables that are read from all tables on which it relies. If there is a circular dependency, MySQL prompts for an error.

· Perform all standard where optimizations.

· If a row in a matches a WHERE clause, but no row in B matches the on condition, then another B row is generated, where all the columns are set to NULL.

· If you use a LEFT join to find a row that does not exist in some tables, and the following test: the where part of the col_name is NULL, where the col_name is a column declared not NULL, MySQL finds a match Stop after one line of the join condition (for a specific keyword combination) to search for additional rows.

The execution of the right join is similar to a left join, but the role of the table is reversed.

Joins the optimizer in the order in which the tables should be joined. Left JOIN and Straight_join forced table read Order can help the Join optimizer work faster because fewer table exchanges are checked. Note that this means that if you perform the following type of query, MySQL performs a full scan of B because the 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 fix is in the reverse order of a, 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 for the resulting null row, the Where condition is always false, and the left join becomes a normal join.

For example, in the following query, if T2.COLUMN1 is the Null,where clause, it will be 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 can be faster because MySQL can use table t2 before table T1 If you can make the query better. To enforce the use of table order, use Straight_join.

And then filter the conditions and then based on the table to establish the index of the relevant query field in the table so that in the case of large data multiple table joint query speed is very fast, you can look at the following example of the wording:

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= ' A left join Sys_berth as B on B.areaid=a.areaid) T join 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) M left 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