MySQL query optimization: a case study of subqueries instead of non primary key join queries

1 pairs of two tables, 1 is usually the foreign key of one table associated with the primary key of the other table. But there are also non-1 cases where two tables are not associated by the primary key of one of the tables.
Such as:

 
create table t_team 
( 
tid int primary key, 
tname varchar(100) 
); 
create table t_people 
( 
pid int primary key, 
pname varchar(100), 
team_name varchar(100) 
); 

The team table and people table are 1-to-many, tname of team is 1-only, pname of people is 1-only, people is associated with team_name and team, tname is not associated with the primary key id.
(PS: whether it makes sense or not, but if it does... . Many tables are designed so that each table has 1 id and uuid, id as the primary key, and uuid as the association, similar to the above.)
Now to query the people and team information where pname is "xxg" :
SELECT * FROM t_team t,

ople p WHERE t.tname=p.team_name AND p.pname='xxg' LIMIT 1;
or
SELECT * FROM t_team t INNER JOIN

ople p ON t.tname=p.team_name WHERE p.pname='xxg' LIMIT 1;
Execution 1, you can query the results, but if the data is large, the efficiency is very low, the execution is slow.
For this kind of join query, subquery is used instead, the query results are the same, but it is more efficient:
SELECT * FROM (SELECT * FROM

ople WHERE pname='xxg' LIMIT 1) p, t_team t WHERE t.tname=p.team_name LIMIT 1;
A large amount of data is filtered in the subquery (only one item is retained), and then the results are connected to the query, which greatly improves the efficiency.
(PS: in addition, using LIMIT 1 can also improve the efficiency of the query, in detail: http: / / blog csdn. net/xiao__gui/article/details / 8726272)
I have tested the efficiency of the two query methods through three SQL tests:
Prepare 10,000 pieces of team data and 1 million pieces of people data.
Stored procedures for creating data:

 
BEGIN 
DECLARE i INT; 
START TRANSACTION; 
SET i=0; 
WHILE i<10000 DO 
INSERT INTO t_team VALUES(i+1,CONCAT('team',i+1)); 
SET i=i+1; 
END WHILE; 
SET i=0; 
WHILE i<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:
Join queries

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

Time:12.594 s
Join queries

 
SELECT * FROM t_team t INNER JOIN t_peoplep ON t.tname=p.team_name WHERE p.pname='people20000' LIMIT 1; 

Time:12.360 s
The subquery

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

Time:0.016 s