Notes on efficiency of Multi-table join queries in SQL

The efficiency of Multi-table join queries has been a topic discussed by our developers. I will share some of my tests when using multi-table queries with you, I hope to help you all.

I encountered A problem when I was working on the website recently. I need to output two fields in Table B and Table C. However, the variable obtained by the program is the id in table, table A is associated with table B and Table C with another field. In this way, I need to read data from three tables. At first I wanted to use SQL multi-table join queries, but I was worried that the efficiency was too low. If I first read the values of the fields associated with B and C in Table A, then run another SQL statement to use this value to read the values in Table B and C, is it more efficient? After thinking for a long time, I decided to write a program for testing.

Run the following code to Discuz! The X2.5 database is used as an example to query the username of the topic issuer whose tid is 1.

The Code is as follows:	Copy code
<? Php $ Start = time (); For ($ I = 1; I I <10000; $ I ++ ){ $ WriterInfo = DB: fetch_first ("SELECT a. username FROM Pre_common_member AS a, pre_forum_thread AS B WHERE B. tid = '1' AND a. uid = B. authorid "); } Echo (time ()-$ start ); ?>

This code saves the current timestamp before executing the SQL query, then executes 10 thousand multi-table join queries, and then uses the timestamp after execution minus the timestamp before the start, get the running time.
Then, change the code in the loop block to the following:

The Code is as follows:	Copy code
$ Tid = DB: fetch_first ("SELECT authorid FROM pre_forum_thread WHERE tid = '1 '"); $ User = DB: fetch_first ("SELECT username FROM pre_common_member WHERE uid = '$ tid [authorid]'");

This time, the SQL statement is executed twice.
To make the results accurate, the two code segments are executed three times, and the execution time of the first code segment is 13, 13, and 15 seconds, respectively, the second code execution time is 23, 21, and 22, respectively.
It can be seen that the multi-table join SQL statement is divided into multiple executions, and the time will be about 80% longer than a single multi-table join. The conclusion is that it is more efficient to directly execute a single row of Multi-table join.

The following are some aside, but they are also about multi-table joint queries.

Inner join, full outer join, left join, right jion
Combination of inner join tables
Full outer is connected to the same combination of two tables. Table A has data that table B does not have (it is displayed as null), and table B has
Table A does not display (null)
Table A left join table B left join, which is based on table A. All data of Table A is combined by table B. Null is not found.
Table A right join table B right join, based on table B, all data of Table B, some combinations of Table. Null is not found.

Query analyzer execution:
-- Create Table table1, table2:
Create table table1 (id int, name varchar (10 ))
Create table table2 (id int, score int)
Insert into table1 select 1, 'lil'
Insert into table1 select 2, 'zhang'
Insert into table1 select 4, 'wang'
Insert into table2 select 1, 90
Insert into table2 select 2,100
Insert into table2 select 3, 70
Such as table
-------------------------------------------------
Table1 | table2 |
-------------------------------------------------
Idname | idscore |
1lee | 190 |
2zhang | 2100 |
4wang | 370 |
-------------------------------------------------

Run the following commands in the query Analyzer:

1. External Connection
1. Concept: including left Outer Join, right Outer Join or complete external join

2. left join: left join or left outer join
(1) The result set of the left outer Join includes all rows in the LEFT table specified in the left outer clause, not just the rows matched by the join column. If a row in the left table does not match a row in the right table, all the selection list columns in the right table in the row of the associated result set are null ).
(2) SQL statements

The Code is as follows:	Copy code
Select * from table1 left join table2 on table1.id = table2.id ------------- Result ------------- Idnameidscore ------------------------------ 1lee190 2zhang2100 4 wangNULLNULL ------------------------------

Note: all the clauses containing Table 1 return the corresponding fields of Table 2 based on the specified conditions. The non-conforming fields are displayed as null.

3. right join: right join or right outer join
(1) The right outer join is the reverse join of the left Outer Join. All rows in the right table are returned. If a row in the right table does not match a row in the left table, a null value is returned for the left table.
(2) SQL statements

The Code is as follows:	Copy code
Select * from table1 right join table2 on table1.id = table2.id ------------- Result ------------- Idnameidscore ------------------------------ 1lee190 2zhang2100 NULLNULL370

------------------------------
Note: all the clauses containing Table 2 return the corresponding fields of Table 1 Based on the specified conditions. The non-conforming fields are displayed as null.

4. Complete External join: full join or full outer join
(1) The Complete External join returns all rows in the left and right tables. If a row does not match a row in another table, the selection list column of the other table contains a null value. If there are matched rows between tables, the entire result set row contains the data value of the base table.
(2) SQL statements

The Code is as follows:	Copy code
Select * from table1 full join table2 on table1.id = table2.id ------------- Result ------------- Idnameidscore ------------------------------ 1lee190 2zhang2100 4 wangNULLNULL NULLNULL370

------------------------------
Note: returns the sum of left and right connections (see upper left and right connections)

2. Internal Connection
1. Concept: inner join is a join that uses a comparison operator to compare the values of the columns to be joined.

2. inner join: join or inner join

3. SQL statements

The Code is as follows:	Copy code
Select * from table1 join table2 on table1.id = table2.id ------------- Result ------------- Idnameidscore ------------------------------ 1lee190 2zhang2100

------------------------------
Note: Only the table1 and table2 columns that meet the conditions are returned.

4. equivalent (same as the following execution)

The Code is as follows:	Copy code
A: select a. *, B. * from table1 a, table2 B where a. id = B. id B: select * from table1 cross join table2 where table1.id = table2.id

(Note: Only the where clause can be used for cross join and the on clause cannot be used)

Iii. Cross-join (complete)

1. Concept: A cross join without a WHERE clause will generate the Cartesian product of the table involved in the join. The number of rows in the first table multiplied by the number of rows in the second table is equal to the size of the Cartesian result set. (Table1 and table2 generate 3*3 = 9 records)

2. cross join: cross join (without the condition where ...)

3. SQL statements

The Code is as follows:	Copy code
Select * from table1 cross join table2 ------------- Result ------------- Idnameidscore ------------------------------ 1lee190 2zhang190 4wang190 1lee2100 2zhang2100 4wang2100 1lee370 2zhang370 4wang370

------------------------------
Note: 3*3 = 9 records are returned, that is, Cartesian product.

4. equivalent (same as the following execution)

The Code is as follows:	Copy code
A: select * from table1, table2