There are five queries here for the UNIVERSITY database, each involving a join of student st, grade_report gr, section se. You are to answer the questions following the queries about the MySQL 5.7 query plan (mostly the join order).

Queries end in "\G" instead of ";" so as to spread the EXPLAIN records out vertically; they take too much space horizontally.

1. Exact match on student.name

explain 
select se.instructor from student st, grade_report gr, section se
where st.student_number = gr.student_number 
and gr.section_identifier = se.section_identifier and st.name='Brown'\G

2. Range match on student.class

explain 
select se.instructor from student st, grade_report gr, section se
where st.student_number = gr.student_number 
and gr.section_identifier = se.section_identifier and st.class < 3\G

3. Exact match on section.instructor

explain
select st.name from student st, grade_report gr, section se
where st.student_number = gr.student_number 
and gr.section_identifier = se.section_identifier and se.instructor = 'Anderson'\G

4. Range match on section.year

explain
select st.name from student st, grade_report gr, section se
where st.student_number = gr.student_number 
and gr.section_identifier = se.section_identifier and se.year >2007\G

5. Range match on section.course_number
("se.course_number LIKE 'Math%'" works the same way)

explain
select st.name from student st, grade_report gr, section se
where st.student_number = gr.student_number 
and gr.section_identifier = se.section_identifier and se.course_number >= 'MATH'\G

Questions
=========

For 1-5, use EXPLAIN to identify, for each query, in what order the two joins are performed by MySQL.

1 and 3 use an exact match on a name; 2 and 4 use range matches on a numeric attribute. In addition to identifying the join order, you should explain whether or not there is any MySQL query-plan difference between 1 and 2 or between 3 and 4.

6. Add an index on section(year) as follows:
	create index yeardex on section(year);
Does the join order change for query 4? Why or why not?

Remove the index with:
	drop index yeardex on section;

7. Add an index on section(instructor) as follows:
	create index instructordex on section(instructor);
Does the join order change for query 3? Why or why not?

Remove the index with:
	drop index instructordex on section;