DBM438 Lab 5 Optimize Queries in MySQL and Analyzing the Database

Description

I. OBJECTIVES
Take a look at various ways to optimize queries in MySQL and various ways that the Data Directory can help in analyzing the database.
II. ASSUMPTIONS
After completing this lab, you will have a general understanding of how MySQL handles queries of different types, how these queries can be analyzed, and how, based on this analysis, the query can be optimized.
III. PROCEDURE
For each lab, it will be necessary for you to create an output file that will capture all of your commands and work within your MySQL session. The steps for doing this are as follows.
Create a folder on the S: drive of your Windows VM named DBM438. This will be where you will save all of your session output files.
Log into a MySQL session at the ‘root’ level. This will be the default beginning level for all of your iLab sessions.
At the mysql> prompt, type the following: tee s:dbm438labxoutputfile.log, where the ‘x’ is the lab number.
Press enter. You should receive a ‘Logging to ‘s:dbm438labxoutputfile.log’ message.
To stop writing to the log you simply type note at the prompt.
Unfortunately you cannot copy this file from your VM to your host computer, so you will need to open the file, copy the contents, and then paste them into the Lab Report document for this lab under the Lab Results section. If you will use Courier New 9pt. font, formatting will look just like in the session.
NOTE: If you have to stop your lab session and go back later, you need to be sure that you are using the same file name for your output file. Doing so will allow MySQL to append the new work to the end of the file, thus allowing you to save all of your lab output into one file. If you do end up using two or more file names, you will need to copy and paste the contents of the second file into the first, and thus just have one file to turn in.
Review the lecture in Week 5 for additional information that can be used for this lab. To begin this lab, open up the Windows Command Prompt and change the prompt to C:>. Now, start a new MySQL session as the root user on localhost. Once you are logged in, start the output file for this lab. You now need to set up the environment for this lab.
Log into eCollege and download the DBM438_leebooks.sql file from Doc Sharing.
Set the Mysql to use the devrydbm438 database.
Drop any and all tables from Lab 4. You will not need to drop the G_DEPT temporary table, as it was dropped automatically when you exited the session for that lab.
Once the database is clean, use the SOURCE command to run the DBM438_leebooks.sql script to load the tables and data for this lab.
Now use the SHOW TABLES command to verify that the tables are there (you should have eight tables listed).
All major database architectures employ some form of query optimizer to help make sure that queries are running as efficiently as possible. With the limited database schema that we are working with, some of the support the optimizer offers will be a little difficult to show, but we can still get a good idea.
To start, we are going to look at how the EXPLAIN EXTENDED command works with a query. This is how we can get the query to take advantage of the information that the optimizer has already. Write and execute, using EXPLAIN EXTENDED, a simple query that will return data from the book_customer table showing the first name, last name, and city for customers in zip code 32106.
Notice the output (I realize it is a little hard to read and follow)—one thing to note is the number of rows compared to return the one row of data. Also, notice the time it took to run the query. Now, execute the query again by itself and compare the time taken. The optimizer is now using information gained from the first run of the query to better execute the query (your second run of the query should have shown 1 row in set (0.00)).
Now we are going to create a scenario where we can compare what happens when we can use the ANALYZE process to give the optimizer valuable information before we run a more complicated query, but we need to get a baseline first.
First, you need to construct a join query using the books, book_author, and author that will display the book title and first and last name of the author. Notice the amount of time that it takes to execute this query the first time it successfully runs. Now, execute the query again, and a third time if necessary until you receive a 0.00 sec return. Why do you think the amount of time to execute the query took less time each subsequent time you executed it? Add content to you iLab Report Conclusions section to address your answer.
Now, we need to reset the environment so that we can replicate this process with a slightly different approach. Be sure that you have saved the successful version of your query before you proceed, as you will need it again.
Now we are going to repeat the process in step 5 but use the ANALYZE utility on the tables involved first and then compare the outcome from the first time the query is run.
We need to reset the environment, so exit out of your MySQL session back to the C:> prompt. Now issue the commands to stop the mysql56 service and then start it back up again. Once it is started, log back into a MySQL session as the root user. Once logged in, start your output file, being sure to use the same file name.
Now, use the ANALYZE utility on the three tables that are being used in the join query you wrote in step 5.
Now execute the actual query and compare the amount of time it took to execute this time compared to what was done in step 5. What do you think caused the difference? Add content to you iLab Report Conclusions section to address your answer.
Now, run the same query and use the EXPLAIN EXTENDED option.
We have already looked at some ways that we can affect change on the name of certain objects in the Data Directory of the MySQL architecture. Now we are going to look at how we can actually relocate things related to the directory.
For this part of the lab, we are going to make a change to the my.ini file and then show the outcomes of that change. To start, you need to exit out of your current session and then once at the C:>, execute the command to stop the mysql56 service.
Now we are going to change the location of the error log for this MySQL server. First, go to the ProgramData folder on your C drive and create a new folder named data2 under the ‘MySQL Server 5.6’ folder. Next, go to the my.ini file that you have used before (should be under C:ProgramDataMySQLMySQL Server 5.6). Open the file and find the log-error parameter in the [mysqld] section of the file. You want to change the file name to DBM438.err and include the full file path to the new data2 folder starting from the C drive (hint: use the file path under the parameter datadir= as a guide). Enclose the full path in double quotes and then save the file.
Now go back to the Windows Command window C:> prompt and issue the command to start the mysql56 service. Once started, then start a new MySQL session as the root user on localhost. Once logged in, start your output file, being sure to use the same file name.
Now, using the SHOW VARIABLES command, find any variables that reference log_error. This should show the new path to your error log file.
This concludes the lab for Week 5. You will need to open the output file for this lab on the S: drive of your VM, copy the contents of the file and then paste them into the Lab Report document for this lab under the Lab Results section. If you use Courier New 9pt. font, formatting will look just like in the session. This completes this lab.
Grading of this lab assignment will be based on the following:
[table]
Description, Points
Objectives section is sufficiently filled out., 5
Steps 3 through 8 were satisfactorily completed., 15
Contents of output file showing session input and results were included in the iLab Report., 15
Conclusions section is sufficiently filled out. ,5
Total Lab Points:, 50
[/table]
SCREENSHOTS
SOLUTION
PAYMENT
The solution includes a zip document.
Attachments [Move over files to preview content of those files]
DBM438_Week_5_Lab.zip (28.25 KB)
DBM438_Week_5_iLab_Report.docx
Price: $19
Buy Now
Checkout
Added to cart
Buy More Save More
Buy at least TWO items & save up to 30% OFF your ENTIRE order!
Rack up instant rebates in your shopping cart. Simply add items to your cart, and see the savings add up.
Discounts will automatically be applied on eligible orders.
DBM438 Lab 5 Optimize Queries in MySQL and Analyzing the Database – $19.00
DBM438 Lab 6 Creating and Managing Users in the Database – $19.00
DBM438 Lab 7 Creating and Managing Database Backups – $19.00
Add to Cart
Checkout
Added to cart
You May Also Like:
DBM438 Lab 1 Comparison of MySQL Oracle and SQL Server
DBM438 Lab 2 Create a new MySQL Database
DBM438 Lab 3 General Analysis and Administration of a MySQL Server and Database
DBM438 Lab 4 Taking a look at Different Data Storage Issues and Basic Table Management
DBM438 Lab 6 Creating and Managing Users in the Database
DBM438 Lab 7 Creating and Managing Database Backups