Mysql functions replace related subqueries (Correlated subquery)

The mysql function replaces the related subquery (Correlated subquery) SQL code CREATE TABLE '2017 _ teacher '('Teacher _ id' int (11) NOT NULL, 'School _ id' int (11) not null, primary key ('Teacher _ id'), KEY '2017 _ teacher_idx_school '('school _ id ')) ENGINE = InnoDB www.2cto.com. there are 1000 teachers in the TABLE, which are randomly distributed in 40 schools. The SQL code CREATE TABLE '192 _ subject_teacher_class '('Teacher _ id' int (11) not null, 'subj' varchar (10) not null, 'class' varchar (10) Not null, primary key ('Teacher _ id', 'subj', 'class') ENGINE = InnoDB instructor class, the instructor randomly teaches three subjects in 24 classes. for ease of demonstration, the subject name and class name are directly put into the database. Suppose you want to query the instructor's teaching information. Each teacher displays English in this way: class 11, Class 12, Class 8 ## language: class 13, class 1, class 21, Class 6 # Mathematics: Class 12, Class 14, Class 6, class 7 can easily think of this SQL statement to show the teaching situation of each instructor. The SQL code select tid, GROUP_CONCAT (cls SEPARATOR '##') c1 from (select teacher_id tid, CONCAT (subj, ':', GROUP_CONCAT (class) cls from 20121105_subject_teacher_class stc group by teache R_id, subj) t group by tid. How about using this as a subquery? It seems easy to think of the SQL code select teacher_id, (select GROUP_CONCAT (cls SEPARATOR '#') from (select teacher_id tid, CONCAT (subj, ':', GROUP_CONCAT (class )) cls from 20121105_subject_teacher_class stc where stc. teacher_id = t1.teacher _ id group by teacher_id, subj) t group by tid) from 20121105_teacher t1 where school_id = 2 but unfortunately, in the innermost layer of the subquery, The teacher_id field of table t1 cannot be referenced, and only the SQL code select teacher_id on the outer layer can be obtained, (select GROUP_CONCAT (cls SEPARATOR '#') from (select teacher_id tid, CONCAT (subj, ':', GROUP_CONCAT (class) cls from 20121105_subject_teacher_class stc group by teacher_id, subj) t where t. tid = t1.teacher _ id group by tid) from 20121105_teacher t1 where school_id = 2. However, as a result, indexes cannot be efficiently used, this SQL takes 0.05 s, so you can CREATE a FUNCTION SQL code create function '2121105f' (p_teacher_id int) RETURNS varchar (2000) reads SQL DATA BEGIN DECLARE v_result VARCHAR (2000 ); declare exit handler for not found return null; select GROUP_CONCAT (cls SEPARATOR '#') into v_result from (select teacher_id tid, CONCAT (subj, ':', GROUP_CONCAT (class )) cls from 20121105_subject_teacher_class stc where stc. teacher_id = p_teacher_id group by teacher_id, subj) t group by tid; return v_result; END then use the SQL code select SQL _NO_CACHE teacher_id, 20121105f (teacher_id) from 20121105_teacher t1 where school_id = 2 is immediately instantaneous. you can also use the left join method without subqueries. SQL code select t1.teacher _ id, t2.c1 from 20121105_teacher t1 left join (select tid, GROUP_CONCAT (cls SEPARATOR '##') c1 from (select teacher_id tid, CONCAT (subj, ':', GROUP_CONCAT (class) cls from 20121105_subject_teacher_class stc group by teacher_id, subj) t group by tid) t2 on t1.teacher _ id = t2.tid where school_id = 2 in this case, because the 20121105_subject_teacher_class table has no index, it is about 0.04s plus the conditional SQL code select t1.teacher _ id, t2.c1 from 20121105_teacher t1 left join (select tid, GROUP_CONCAT (cls SEPARATOR '##') c1 from (select stc. teacher_id tid, CONCAT (subj, ':', GROUP_CONCAT (class) cls from 20121105_subject_teacher_class stc, 20121105_teacher te where stc. teacher_id = te. teacher_id and te. school_id = 2 group by stc. teacher_id, subj) t group by tid) t2 on t1.teacher _ id = t2.tid where school_id = 2 This also becomes instantaneous, but filter the conditions of teacher (school_id = 2) executed twice. If this condition is resource-consuming, it should be slower.