MIS561 Lab 5 Optimizing query using EXPLAIN

Description

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
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 = valueG
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 outerG
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 = valueG
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 WARNINGSG
10) STEP 10: Explain UPDATE statement – test EXPLAIN #9
Example:
Explain update table_name set column_name = valueG
Explain update mis561.employee set table1col = ‘val’G
Explain output of EXPLAIN. Save the screenshot.
SCREENSHOTS
SOLUTION
PAYMENT
The solution includes a zip document.
Attachments [Move over files to preview content of those files]
MIS561_Lab_5.zip (420.94 KB)
MIS561 Lab 5 Report.doc
MIS561_Lab_5_Script.sql
Preview MIS561 Lab 5 Report.doc
xxxxxxx #1 xxxxxxxxx:
xxxxxxx xxxxxx xxxxxx xxxx xxxxxxxx xxxxx xxxxx = 3;
xxxxxxx xxxxx xxx xxxxxx xxxxxxxxxxx xxxxx xxx xxxxx xxxxxxxx. xxxxxxxxxxx xxxxxxx xx xxxxx(x) & xxx(x) xxxx, xx xx xxxx xxxxxxxx xxx xxxxxxxxx xxxx xx xxx xxxxx.
xxxx 2
Explain output of EXPLAIN #2 statement.
Present screenshot with EXPLAIN #2 statement results.
EXPLAIN #2 statement:
EXPLAIN SELECT *FROM Employee WHERE EMPNO IN (1,3,5);

When IN operator is used to select the values from a table, then the type of the query as shown in image above will be �range�. Index range scan will be used in this query and following is the graphical execution plan of the query.

STEP 3
Explain output of EXPLAIN #3 statement.
Present screenshot with EXPLAIN #3 statement results.
EXPLAIN #3 statement:
EXPLAIN SELECT *FROM Employee WHERE JOBCATEGORY LIKE ‘dev%’;
xxxx �xxxx� xxxxxxxx xx xxxx xxx xxxx xx xxxxx xx xxxxxx xxx xxxxx xxxx xx xxxxx xxxxxx xx xxxxxxxxx xxx xxxxxxx xxx xxxxxx �xxxxxxxxxxx� xxx xxxxxxxxx xx xxx xxxxxxxxx xxxxxxxxx xxxx xx xxx xxxxx.
xxxx 4
xxxxxxx xxxxxx xx xxxxxxx #4 xxxxxxxxx.
xxxxxxx xxxxxxxxxx xxxx xxxxxxx #4 xxxxxxxxx xxxxxxx.
Preview MIS561_Lab_5_Script.sql
xxx xxx561;
— xxxx 1
xxxxxxx xxxxxx xxxxxx xxxx xxxxxxxx xxxxx xxxxx = 3;
— xxxx 2 xxxxxx xxxxxxx xx xxxx #1
— EXPLAIN SELECT *FROM Employee WHERE EFNAME IN (‘Jhon’, ‘James’,’Nina’); EXPLAIN SELECT *FROM Employee WHERE EMPNO IN (1,3,5);
— STEP 3 – LIKE EXPLAIN SELECT *FROM Employee WHERE JOBCATEGORY LIKE ‘dev%’; — EXPLAIN SELECT *FROM Employee WHERE EMPNO = 1 OR EMPNO = 3 OR EMPNO = 5;
— xxxx 4:
xxxxxxx xxxxxx xxxxxxxxxxx, xxxxxxxx xxxx xxx xxxxx xxxxx (xxxxxxxx, xxxxxxx) xxxxxxx (‘xxxxxxxxx’);
— xxxx 5;
xxxxxxx xxxxxx xxxxxxxx xxxx xxxxxxxx xxxxx xxxxx = 5;
Price: $19
Buy Now
Checkout
Added to cart
Add to Cart
Checkout
Added to cart
Note: We offer a discount for buyers who purchase multiple items.
You May Also Like:
MIS561 Lab 1 Installing MySQL Community Server and MySQL Workbench
MIS561 Lab 2 Work with Dictionary and Create Relational Database
MIS561 Lab 3 Users Privileges and Roles
MIS561 Lab 4 Using Backups for Complete Recovery