IT360 Lab 14 - DB Performance

 

DUE: Monday April 28, 2014, before 2359

 

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.

 

    1. Compute the number of pages needed to store the entire table on disk

 

    1. Compute the approximate time  needed to evaluate the following query, assuming no indexes are used:

 

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.

 

 

 

Exercise 2:

 

·         Consider a table with the following schema

StudentsEnroll(StudentID, CourseID, ACYearEnd, Semester).

 

Consider the query:

 

SELECT CourseID, Count(*) AS NumStudents

FROM StudentsEnroll

WHERE ACYearEnd = 2014 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

Electronic (due before 2359 on April 28, 2014):

  1. Upload yourlastname_yourfirstname_Lab14.txt with all your answers to the Lab 14 assignment on the blackboard.

 

Hard-copies (due before start of lab on April 29, 2014):

  1. The completed assignment coversheet. Your comments will help us improve the course.
  2. A hard copy of your yourlastname_yourfirstname_Lab14.txt containing the answers for each exercise.