MIS561 Lab 5 in MySQL – Optimizing query using EXPLAIN – Instant Delivery – Perfect Solution

Lab Price = $15
Please feel free to send us your queries at: [email protected]
Payment methods

Add to Cart

Buy Now

View Cart


Problem Statement

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.

Relevant Material
Screenshots
Lab 5: Screenshot2
Lab 5: Screenshot2

Lab 5: Screenshot1
Lab 5: Screenshot1
Instructions
* If you want to purchase multiple products then click on “Buy Now” button which will give you ADD TO CART option.Please note that the payment is done through PayPal.
* You can also use 2CO option if you want to purchase through Credit Cards/Paypal but make sure you put the correct billing information otherwise you wont be able to receive any download link.
* Your paypal has to be pre-loaded in order to complete the purchase or otherwise please discuss it with us at [email protected].
* As soon as the payment is received, download link of the solution will automatically be sent to the address used in selected payment method.
* Please check your junk mails as the download link email might go there and please be patient for the download link email. Sometimes, due to server congestion, you may receive download link with a delay.
* All the contents are compressed in one zip folder.
* In case if you get stuck at any point during the payment process, please immediately contact us at [email protected] and we will fix it with you.
* We try our best to reach back to you on immediate basis. However, please wait for atleast 8 hours for a response from our side. Afterall, we are humans.
* Comments/Feedbacks are truely welcomed and there might be some incentives for you for the next lab/quiz/assignment.
* In case of any query, please donot hesitate to contact us at [email protected].
* MOST IMPORTANT Please use the tutorials as a guide and they need NOT to be used for any submission. Just take help from the material.
******************************************** Good Luck ***************************************************
Privacy Policy
We take your privacy seriously and will take all measures to protect your personal information.
Any personal information received will only be used to fill your order. We will not sell or redistribute your information to anyone.
Refund Policy
Incase you face any issues with the tutorial, please free to contact us on [email protected]
We will try our best to resolve the issue and if still persists we can discuss for a refund in case its required.
Payment Details
Lab Price = $15
Please feel free to send us your queries at: [email protected]

Payment methods

Add to Cart

Buy Now

View Cart

Leave a Reply