A one-to-many list of two tables, typically a foreign key of a table, is associated with the primary key of another table. But there are also unusual situations where two tables are not associated with a primary key in one of the tables.
For example:
Copy Code code as follows:
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)
);
Team table and People table is a one-to-many relationship, Team Tname is unique, people pname is unique, people table foreign key team_name and the team Table Tname Association, is not associated with the primary key ID.
PS: Let's not say that this design is not reasonable, but if it is really on the stand ... many tables are designed to have an ID and uuid,id as a primary key for each table, and a UUID to associate with it, similar to the above.
Now to query the people and team information for PName is "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;
Execute, you can query the results, but if the data is large, the efficiency is very low, execution is very slow.
For this kind of connection query, the subquery is used instead, the query results are the same, but more efficient:
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), and then connects the results to the query, the efficiency will be greatly improved.
(PS: In addition, the use of limit 1 can also improve query efficiency, detailed: http://blog.csdn.net/xiao__gui/article/details/8726272)
I test the efficiency of two query methods through 3 sql:
Prepare 10,000 team data and prepare 1 million people data.
Stored procedures for making data:
Copy Code code as follows:
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:
Connection Query
Copy Code code as follows:
SELECT * from T_team t,t_people p WHERE t.tname=p.team_nameand p.pname= ' people20000 ' LIMIT 1;
time:12.594 s
Connection Query
Copy Code code as follows:
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
Child query
Copy Code code as follows:
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
