[NEW SOLN] MIS561 LAB 5 OPTIMIZING QUERY USING EXPLAIN

[SOLVED] MIS561 LAB 5 OPTIMIZING QUERY USING EXPLAIN: 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

[SOLVED] MIS561 LAB 5 OPTIMIZING QUERY USING EXPLAIN: 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

[SOLVED] MIS561 LAB 5 OPTIMIZING QUERY USING EXPLAIN: 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.

[SOLVED] MIS561 LAB 5 OPTIMIZING QUERY USING EXPLAIN: 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.

[SOLVED] MIS561 LAB 5 OPTIMIZING QUERY USING EXPLAIN:

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.

[NEW SOLN] MIS561 LAB 5 OPTIMIZING QUERY USING EXPLAIN

You May Also Like