MySQL Query Optimization: use subqueries instead of non-primary key connections to query instances _ MySQL

MySQL Query Optimization: Use a subquery instead of a non-primary key to connect to the query instance to introduce bitsCN.com one-to-many two tables. Generally, the foreign key of one table is associated with the primary key of another table. However, there are also some unusual situations, that is, two tables are not joined by the primary key of one of the tables.
For example:

Create table t_team
(
Tid int primary key,
Tname varchar (100)
);
Create table t_people
(
Pid int primary key,
Pname varchiar (100 ),
Team_name varchar (100)
);

The team table and the people table are in a one-to-multiple relationship. The team tname is unique and the people pname is unique. the Chinese and foreign keys of the people table are associated with the team table tname, it is not associated with the primary key id.
(PS: Let's not say that such a design combination is unreasonable, but if it's true ..... Many tables are designed to have an id and uuid, id as the primary key, and uuid as the association, which is similar to the above)
Query the people and team information of pname "xxg:
SELECT * FROM t_team t, t_people p WHERE t. tname = p. team_name AND p. pname = 'xxg' LIMIT 1;
Or
SELECT * FROM t_team t inner join t_people p ON t. tname = p. team_name WHERE p. pname = 'xxg' LIMIT 1;
Run the following command to query the results. However, if the data volume is large, the execution is slow and the efficiency is low.
For this connection query, the subquery is used instead. the query results are the same, but the efficiency is higher:
SELECT * FROM (SELECT * FROM t_people WHERE pname = 'xxg' LIMIT 1) p, t_team t WHERE t. tname = p. team_name LIMIT 1;
The subquery filters a large amount of data (only one record is retained) and then connects the results to the query, which greatly improves the efficiency.
(PS: In addition, the use of LIMIT 1 can also improve the query efficiency, details: http://blog.csdn.net/xiao__gui/article/details/8726272)
I tested the efficiency of two query methods through three SQL statements:
Prepare 10 thousand pieces of team data and 1 million pieces of people data.
Data creation and storage process:

BEGIN
DECLARE I INT;
Start transaction;
SET I = 0;
WHILE I & lt; 10000 DO
Insert into t_team VALUES (I + 1, CONCAT ('team', I + 1 ));
SET I = I + 1;
End while;
SET I = 0;
WHILE I & lt; 1000000 DO
Insert into t_people VALUES (I + 1, CONCAT ('people', I + 1), CONCAT ('team', I % 10000 + 1 ));
SET I = I + 1;
End while;
COMMIT;
END

SQL statement execution efficiency:
Connection query

SELECT * FROM t_team t, t_people p WHERE t. tname = p. team_nameAND p. pname = 'lele20000 'LIMIT 1;

Time: 12.594 s
Connection query

SELECT * FROM t_team t inner join t_peoplep ON t. tname = p. team_name WHERE p. pname = 'lele20000 'LIMIT 1;

Time: 12.360 s
Subquery

SELECT * FROM (SELECT * FROM t_people WHEREpname = 'lele20000 'LIMIT 1) p, t_team t WHERE t. tname = p. team_name LIMIT 1;

Time: 0.016 sbitsCN.com