Detailed description of Mysql multi-table combined query efficiency analysis and optimization _ MySQL

This article mainly introduces the efficiency analysis and optimization of Mysql multi-table joint query. For more information, see the following code:

SELECT column_name FROM table1 LEFT [OUTER] JOIN table2 ON table1.column = table2.column
B. RIGHT [OUTER] JOIN:

The difference between RIGHT and left join is that in addition to displaying results that meet the connection conditions, you also need to display data columns that do not meet the connection conditions in the RIGHT table.

The code is as follows:

SELECT column_name FROM table1 RIGHT [OUTER] JOIN table2 ON table1.column = table2.column

Tips:

The code is as follows:

SELECT * FROM table1, table2 WHERE table1.id = table2.id;

2. ON

Mysql>

The code is as follows:

SELECT * FROM table1 left join table2 ON table1.id = table2.id;

SELECT * FROM table1 left join table2 ON table1.id = table2.id
Left join table3 ON table2.id = table3.id;

3. USING clause. if the two columns of the join two tables have the same names, you can use USING

For example:

Select from left join using ()

Examples of connecting more than two tables:

Mysql>

SELECT artists.Artist, cds.title, genres.genre   FROM cds   LEFT JOIN genres N cds.genreID = genres.genreID   LEFT JOIN artists ON cds.artistID = artists.artistID; 

Or mysql>

SELECT artists.Artist, cds.title, genres.genre   FROM cds   LEFT JOIN genres ON cds.genreID = genres.genreID    LEFT JOIN artists -> ON cds.artistID = artists.artistID   WHERE (genres.genre = 'Pop'); 

--------------------------------------------

The code is as follows:

SELECT * FROM t1 left join t2 ON (column1) WHERE t2.column2 = 5;

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

The code is as follows:

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.

The above is all the content of this article, hoping to help you learn.