IT360 Lab 14 - DB Performance
DUE: Thursday May 2, 2013, before 1200
This lab should get you familiarized with indexing and query efficiency.
Create a file called yourlastname_yourfirstname_Lab14.txt that will contain all your answers for this lab. Write your name at the top of the file.
Exercise 1: Consider a disk with average I/O time of 15 milliseconds per page and page size = 4096 bytes. For all of the following questions, the time needed to answer a query is considered to be the number of pages accessed * average I/O time per page.
Consider a table Products(BarCode, PName, Price) with 200,000 rows of 100 bytes each. The table is stored on disk such that no row spans 2 pages.
SELECT * FROM Products
c. Consider the following query:
SELECT * FROM Products WHERE Price between 10 and 100
Assume that a B+tree index exists on Price, and 75% of the products satisfy the condition in the WHERE clause (Price between 10 and 100). For the following questions, you can ignore the cost of retrieving the index entries. The only cost of the operation comes from retrieving the rows (after the “pointers” are found in the index).
c1. Assume that the B+tree index is not clustered. Compute the approximate time (in seconds) needed to evaluate the query using the unclustered B+tree index.
c2. Assume now that the B+tree index is clustered. Compute the approximate time (in seconds) needed to evaluate the query using the clustered index.
c3. Compute the approximate time (in seconds) needed to evaluate the query without using any index.
· Consider a table with the following schema
StudentsEnroll(StudentID, CourseID, ACYearEnd, Semester).
Consider the query:
SELECT CourseID, Count(*) AS NumStudents
WHERE ACYearEnd = 2013 and Semester = 'SPRING'
GROUP BY CourseID;
a. What index (if any) would you create to improve the efficiency of the query? Justify your answer, and state any assumptions that you make.
b. Write the SQL statement to create the above index in MySQL.
Turn in both electronic and paper version (due before 1200 on May 2, 2013):