Explain the query

+----+-------------+----------------+--------+---------------------------------------+------+---------+---------------------------------------------------------------+---------+-------------+
| id | select_type | table          | type   | possible_keys                         | key  | key_len | ref                                                           | rows    | Extra       |
+----+-------------+----------------+--------+---------------------------------------+------+---------+---------------------------------------------------------------+---------+-------------+
|  1 | PRIMARY     | <derived2>     | system | NULL                                  | NULL | NULL    | NULL                                                          |       1 |             |
|  1 | PRIMARY     | ps_class_instr | ALL    | CRSE_ID,STRM,FULL                     | NULL | NULL    | NULL                                                          | 1499200 |             |
|  1 | PRIMARY     | Course         | ref    | CRSE_ID,STRM,FULL,SUBJECT_AND_CATALOG | FULL | 778     | august.ps_class_instr.CRSE_ID,august.ps_class_instr.STRM,func |       1 | Using where |
|  2 | DERIVED     | Course         | ALL    | NULL                                  | NULL | NULL    | NULL                                                          | 1434601 |             |
+----+-------------+----------------+--------+---------------------------------------+------+---------+---------------------------------------------------------------+---------+-------------+

filter first approach

select
	Course.CRSE_ID,
	Course.STRM,
	ENRL_TOT,
	EMPLID as 'INSTR',
	ACTUAL_ENRL_TOT as 'COURSE_ENRL_TOT',
	GRDE_ENRL_TOT as 'GRDE_ENRL_TOT',
	Course.CLASS_SECTION as 'CLASS_SCTN',
	CLASS_NBR as 'CLASS_NBR',
	ACAD_GROUP as 'ACAD_GROUP',
	SUBJECT as 'SBJ',
	CATALOG_NBR as 'CATALOG_NBR',
	DESCR,
	ROUND(GRADE_AVG, 3) as GRADE_AVG,
	GRADE_AVG_RANK,
	(@total - GRADE_AVG_RANK)/@total as 'GRADE_AVG_PCT',
	ROUND(GPA, 3) as GPA,
	ROUND(DIFFICULTY01, 3) as DIFF1,
	round(UNIT_AVG, 2) as 'Q_UNIT_AVG'
from (select * from Course where SUBJECT='CS' and CATALOG_NBR LIKE '221%') Course
join
(select * from ps_class_instr where INSTR_ROLE='PI') ps_class_instr
on
Course.CRSE_ID=ps_class_instr.CRSE_ID and Course.STRM = ps_class_instr.STRM and Course.CLASS_SECTION = ps_class_instr.CLASS_SECTION
cross join
(select @total:=max(GRADE_AVG_RANK) from Course) Setup;

query

select {FIELDS_TO_SHOW}
from Course
join
ps_class_instr
using(CRSE_ID, STRM, CLASS_SECTION), {SETUP} Setup
where 
SUBJECT='{SUBJECT}' and CATALOG_NBR LIKE '{CATALOG_NBR}%'

real SQL

select
	Course.CRSE_ID,
	Course.STRM,
	ENRL_TOT,
	EMPLID as 'INSTR',
	ACTUAL_ENRL_TOT as 'COURSE_ENRL_TOT',
	GRDE_ENRL_TOT as 'GRDE_ENRL_TOT',
	Course.CLASS_SECTION as 'CLASS_SCTN',
	CLASS_NBR as 'CLASS_NBR',
	ACAD_GROUP as 'ACAD_GROUP',
	SUBJECT as 'SBJ',
	CATALOG_NBR as 'CATALOG_NBR',
	DESCR,
	ROUND(GRADE_AVG, 3) as GRADE_AVG,
	GRADE_AVG_RANK,
	(@total - GRADE_AVG_RANK)/@total as 'GRADE_AVG_PCT',
	ROUND(GPA, 3) as GPA,
	ROUND(DIFFICULTY01, 3) as DIFF1,
	round(UNIT_AVG, 2) as 'Q_UNIT_AVG'
from Course
join
ps_class_instr
on
Course.CRSE_ID=ps_class_instr.CRSE_ID and Course.STRM = ps_class_instr.STRM and Course.CLASS_SECTION = ps_class_instr.CLASS_SECTION
cross join
(select @total:=max(GRADE_AVG_RANK) from Course) Setup
where 
SUBJECT='CS' and CATALOG_NBR LIKE '221%';

SHOW CREATE TABLE Course\G;

*************************** 1. row ***************************
       Table: Course
Create Table: CREATE TABLE `Course` (
  `CRSE_ID` int(11) DEFAULT NULL,
  `STRM` int(11) DEFAULT NULL,
  `CLASS_SECTION` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  `INSTITUTION` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  `ACAD_GROUP` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  `SUBJECT` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  `CATALOG_NBR` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  `ACAD_CAREER` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  `DESCR` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  `CLASS_NBR` int(11) DEFAULT NULL,
  `SSR_COMPONENT` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  `ENRL_CAP` int(11) DEFAULT NULL,
  `ENRL_TOT` int(11) DEFAULT NULL,
  `WAIT_TOT` int(11) DEFAULT NULL,
  `CAMPUS` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  `LOCATION` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  `ACAD_ORG` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  `INSTRUCTION_MODE` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  `START_DT` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  `CANCEL_DT` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  `GRADE_AVG` float DEFAULT NULL,
  `GPA` float DEFAULT NULL,
  `UNIT_AVG` float DEFAULT NULL,
  `DIFFICULTY01` float DEFAULT NULL,
  `DIFFICULTY02` float DEFAULT NULL,
  `DIFFICULTY03` float DEFAULT NULL,
  `DIFFICULTY04` float DEFAULT NULL,
  `DIFFICULTY05` float DEFAULT NULL,
  `DIFFICULTY06` float DEFAULT NULL,
  `DIFFICULTY07` float DEFAULT NULL,
  `DIFFICULTY08` float DEFAULT NULL,
  `DIFFICULTY09` float DEFAULT NULL,
  `DIFFICULTY_CSP` float DEFAULT NULL,
  `DIFFICULTY_DOSA` float DEFAULT NULL,
  `DIFFICULTY_EARTH` float DEFAULT NULL,
  `DIFFICULTY_EDUC` float DEFAULT NULL,
  `DIFFICULTY_ENGR` float DEFAULT NULL,
  `DIFFICULTY_GR` float DEFAULT NULL,
  `DIFFICULTY_GSB` float DEFAULT NULL,
  `DIFFICULTY_HS` float DEFAULT NULL,
  `DIFFICULTY_LAW` float DEFAULT NULL,
  `DIFFICULTY_MED` float DEFAULT NULL,
  `DIFFICULTY_SCS` float DEFAULT NULL,
  `DIFFICULTY_SLAC` float DEFAULT NULL,
  `DIFFICULTY_UG` float DEFAULT NULL,
  `DIFFICULTY_VPSA` float DEFAULT NULL,
  `DIFFICULTY_VPUE` float DEFAULT NULL,
  `GRADE_AVG_RANK` int(11) DEFAULT NULL,
  `GPA_RANK` int(11) DEFAULT NULL,
  `UNIT_AVG_RANK` int(11) DEFAULT NULL,
  `STDNT_AVG_GPA` float DEFAULT NULL,
  `GRDE_ENRL_TOT` int(11) DEFAULT NULL,
  `ACTUAL_ENRL_TOT` int(11) DEFAULT NULL,
  KEY `CRSE_ID` (`CRSE_ID`),
  KEY `STRM` (`STRM`),
  KEY `CLASS_NBR` (`CLASS_NBR`,`STRM`),
  KEY `SSR_COMPONENT` (`SSR_COMPONENT`),
  KEY `FULL` (`CRSE_ID`,`STRM`,`CLASS_SECTION`),
  KEY `SUBJECT_AND_CATALOG` (`SUBJECT`,`CATALOG_NBR`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

SHOW CREATE TABLE ps_class_instr\G;

*************************** 1. row ***************************
       Table: ps_class_instr
Create Table: CREATE TABLE `ps_class_instr` (
  `CRSE_ID` varchar(6) NOT NULL,
  `CRSE_OFFER_NBR` int(38) NOT NULL,
  `STRM` varchar(4) NOT NULL,
  `SESSION_CODE` varchar(3) NOT NULL,
  `CLASS_SECTION` varchar(4) NOT NULL,
  `CLASS_MTG_NBR` int(38) NOT NULL,
  `INSTR_ASSIGN_SEQ` int(38) NOT NULL,
  `EMPLID` varchar(255) DEFAULT NULL,
  `INSTR_ROLE` varchar(4) NOT NULL,
  `GRADE_RSTR_ACCESS` varchar(1) NOT NULL,
  `CONTACT_MINUTES` int(38) NOT NULL,
  `SCHED_PRINT_INSTR` varchar(1) NOT NULL,
  `INSTR_LOAD_FACTOR` float(10,4) NOT NULL,
  `EMPL_RCD` int(38) NOT NULL,
  `ASSIGN_TYPE` varchar(3) NOT NULL,
  `WEEK_WORKLOAD_HRS` float(5,2) NOT NULL,
  `ASSIGNMENT_PCT` float(5,2) NOT NULL,
  `AUTO_CALC_WRKLD` varchar(1) NOT NULL,
  KEY `CRSE_ID` (`CRSE_ID`),
  KEY `STRM` (`STRM`),
  KEY `FULL` (`CRSE_ID`,`STRM`,`CLASS_SECTION`),
  KEY `INSTR_ROLE` (`INSTR_ROLE`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

size

mysql> select count(*) from Course;
+----------+
| count(*) |
+----------+
| 1430783 |
+----------+
1 row in set (0.63 sec)
mysql> select count(*) from ps_class_instr;
+----------+
| count(*) |
+----------+
| 1501554 |
+----------+