For slow SQL Analysis steps: 1) Desc|explain SQL view execution plan, for type very slow, analysis whether to establish the corresponding field index 2) to exclude the method, the query extraction out, separate execution, positioning slow query is which sub-query caused. Specially optimized . script: CREATE TABLE stu (ID BIGINT () PRIMARY key auto_increment COMMENT ' primary key ', NAME VARCHAR (not NULL DEFAULT) ' COMMENT ' name ', phone BIGINT (+) NULL COMMENT ' phone ', UNIQUE KEY idx_phone (phone) ENGINE INNODB DEFAULT CHARSET UTF8 COMMENT ' Test _ Student table '; create table course (ID BIGINT () PRIMARY key auto_increment COMMENT ' primary key ', NAME VARCHAR () not NULL Defau LT ' COMMENT ' name ') ENGINE INNODB DEFAULT CHARSET UTF8 COMMENT ' test _ timetable '; create TABLE stu_course (id BIGINT) Primar Y key auto_increment COMMENT ' primary key ', stu_id BIGINT (a) NOT NULL COMMENT ' student ID ', cous_id BIGINT (a) NOT NULL COMMENT ' course ID ', SCO Re INT (one) not NULL default 0 COMMENT ' fractional ', key idx_stu_id (stu_id), key idx_cous_id (cous_id)) ENGINE INNODB default CHARSET UTF8 COMMENT ' Test _ Student Curriculum Association table '; 1. OR change UNION all before: Select ID from stu where id=10 OR id=20: Select ID from stu where id=10union allselect ID from Stu where I D=20 2. In (subquery) becomes INNER join before: Select ID from Stu WHERE ID in (select stu_id from Stu_course): Select ID from Stu Sinner JOIN (SEL ECT stu_id from Stu_course) tmp on s.id = tmp.stu_id 3. Not in (subquery) changed to left join before: Select ID from Stu WHERE ID not in (select stu_id from Stu_course): Select ID from Stu sleft JOI N (SELECT stu_id from Stu_course) tmp in s.id = Tmp.stu_idwhere tmp.stu_id is null 4. In EXISTS before: Select ID from Stu where ID in (select stu_id from Stu_course): Select ID from stu s WHERE EXISTS (select SC. stu_id from Stu_course sc WHERE sc.stu_id = s.id)
MySQL Simple SQL optimization example [not timed update]
