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

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.

Attachments: