![[SOLVED] MIS561 LAB 5 OPTIMIZING QUERY USING EXPLAIN: EXPLAIN shows information about the database: the number of tables; how tables are joined;](https://grademerit.com/wp-content/uploads/edd/2023/12/Screenshot-2023-12-20-030728.png)
[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.
![[SOLVED] MIS561 LAB 1 INSTALLING MYSQL COMMUNITY SERVER AND MYSQL WORKBENCH: Part 1: Installing MySQL Installer and MySQL Workbench](https://grademerit.com/wp-content/uploads/edd/2023/12/Screenshot-2023-12-20-073420.png)
[SOLVED] MIS561 LAB 1 INSTALLING MYSQL COMMUNITY SERVER AND MYSQL WORKBENCH: Part 1: Installing MySQL Installer and MySQL Workbench
![[SOLVED] MIS561 LAB 2 WORK WITH DICTIONARY AND CREATE RELATIONAL DATABASE: In this lab, you will prepare a Data Dictionary based on the list of elements.](https://grademerit.com/wp-content/uploads/edd/2023/12/Screenshot-2023-12-20-072512.png)
[SOLVED] MIS561 LAB 2 WORK WITH DICTIONARY AND CREATE RELATIONAL DATABASE: In this lab, you will prepare a Data Dictionary based on the list of elements. Also, your task will be determined the tables, their relationships, primary and foreign keys. Based on this analysis, you will create Database Schema, relational tables, Entity –Relational Diagram (ERD), establish connection to your local MySQL Server, create physical database and insert data to the tables.
![[SOLVED] MIS561 LAB 3 USERS PRIVILEGES AND ROLES: MySQL, like many other databases, uses the combination of specific user definitions, privileges, and roles to control access to the data in the database.](https://grademerit.com/wp-content/uploads/edd/2023/12/Screenshot-2023-12-20-071937.png)
[SOLVED] MIS561 LAB 3 USERS PRIVILEGES AND ROLES: MySQL, like many other databases, uses the combination of specific user definitions, privileges, and roles to control access to the data in the database. In turn, it provides various layers of security. When the database is first created, there are several users created for the purpose of not only installing various components of the database, but also to manage and administer the database functionality.
