iLab 5 Optimizing query using EXPLAIN (60 Points)
Note!
Submit your assignment to the Dropbox.
(See the Syllabus section “Due Dates for Assignments & Exams” for due dates.)
Remember This!
iLAB OVERVIEW
Scenario and Summary
EXPLAIN shows information about the database: the number of tables; how tables are joined; how data is looked up (full table scan, full index scan or partial index scan); the presence of subqueries, sorts, and unions; DISTINCT and WHERE clauses are used; indexes used and their length (longer index – longer search); number of records examined
Deliverables
Grading of the lab assignment will be based on the following.
Assignment Step Description Points
Step 1 EXPLAIN #1 – describe Output of EXPLAIN
6
Step 2 EXPLAIN #2 to have type as partial index scan 6
Step 3 EXPLAIN #3 to have type as index_merge 6
Step 4 EXPLAIN #4 to have type = fulltext 6
Step 5 EXPLAIN #5 to have type as const 6
Step 6 EXPLAIN #6 Sample subquery example
6
Step 7 EXPLAIN #7 – describe Output of EXPLAIN with join 6
Step 8 EXPLAIN #8 – EXPLAIN EXTENDED 6
Step 9 Explain warnings using SHOW WARNINGS
6
Step 10 Explain UPDATE statement 6
Total iLab Points 60 Points
Submit your lab session–showing any queries, and other SQL code, and the resulting return from the database–to the Dropbox for the Week 5 iLab.

iLAB STEPS
1) STEP 1: EXPLAIN Output – test EXPLAIN #1
Mysql> EXPLAIN select column from database.tablename where column = value\G
\G – capital letter G places the result set vertically
Explain the meaning and values of result columns (id, table, type, possible_keys, key, key_len, ref, rows, Extra)
Save the screenshot.
2) STEP 2: Modify EXPLAIN in step #1 – test EXPLAIN #2 to have type as partial index scan by including one of the following:
<, <=, >, >=, IS NULL, BETWEEN, IN
Explain output of EXPLAIN. Save the screenshot.
3) STEP 3: Modify EXPLAIN in step #1 – test EXPLAIN #3 to have type as index_merge by including LIKE statement
Explain output of EXPLAIN. Save the screenshot.
4) STEP 4: Modify EXPLAIN #1 – test EXPLAIN #4 to have type = fulltext data access
To write SELECT which causes fulltext data access –select job_category and job_title of the job with title or description which include the word ‘programmer’.
Example:
EXPLAIN select job_category, job_title from bonus where MATCH (job_title, job_description) AGAINST (‘programmer’)\G
Explain output of EXPLAIN. Save the screenshot.
5) STEP 5: Modify EXPLAIN #1 – test EXPLAIN #5 to have type as const by joining/looking up unique index values (index fields compared with =)
Example:
EXPLAIN select hire_date from employee where employee_id = 1234;
Explain output of EXPLAIN. Save the screenshot.
6) STEP 6: Sample subquery example – test EXPLAIN #6
Write EXPLAIN select statement using subquery.
Example:
EXPLAIN select employee_id, employee_name IN (select job_category from bonus AS bonus_subquery where bon_comm IS NULL) from employee AS outer\G
Explain output of EXPLAIN. Save the screenshot.
7) STEP 7: Turn subquery in #6 to join – test EXPLAIN #7.
Explain output of EXPLAIN. Save the screenshot.
8) STEP 8: To know the approximate number of examined rows to be returned, modify EXPLAIN #1 to EXPLAIN EXTENDED – test EXPLAIN #8
Mysql> EXPLAIN EXTENDED select column from database.tablename where column = value\G
Explain output of EXPLAIN EXTENDED (values of rows and filtered columns)
9) STEP 9: Explain warnings received in step #8 by using command:
Mysql>SHOW WARNINGS\G
10) STEP 10: Explain UPDATE statement – test EXPLAIN #9
Example:
Explain update table_name set column_name = value\G
Explain update mis561.employee set table1col = ‘val’\G
Explain output of EXPLAIN. Save the screenshot.