Optimizing query using EXPLAIN

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.

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.

1