MCS-274 Homework (Spring 2005)

After the second test - final revisions due by 12:30 on May 18th

Problem 9.5, p. 375
Problem 9.16, p. 376
Problem 9.17, p. 376
Problem 9.x1: Using our MySQL database, I created a table as follows:
```
create table foo (
 x int not null,
 y int not null,
 primary key (x));
```
I then loaded in a file containing 10,000 rows of data. The x values were the integers from 1 to 10,000 and the y value in each row was the same as that row's x value. The query
```
select * from foo where x = 1;
```
took 0.00 seconds to execute, whereas
```
select * from foo where y = 1;
```
took 0.01 seconds. Increasing the number of rows to 100,000 left the first time 0.00 seconds, but increased the second to 0.10. When I tried changing the selection criteria to use a different number in place of 1 (such as looking for x = 100000, or looking for y = 100000), this didn't change the times appreciably. Explain these observations.
Problem 10.8, p. 402
Problem 10.13, p. 403
Problem 11.x1: Using your movie database in MySQL, give the command
```
explain 
  select * from movies m, acts_in a, persons p 
    where m.id = a.movie and p.id = a.actor 
    and p.fnames = 'Orson'
    and m.year_made < 1990;
```
to get an execution plan. Using the MySQL documentation, translate the execution plan into English. Without adding any indexes, how else might the query reasonably be executed? Do you agree with MySQL's choice?
Problem 12.x1: If the above query is to be executed frequently, and is a performance bottleneck, which index is more likely to be useful to add: an index on the movies table's year_made column, or one on the persons table's fnames column? Assume either index would be an unclustered B⁺ tree. Explain your reasoning.
Problem 12.4, p. 452 (typo correction: the k in the 4k byte page size should not be a superscript)
Problem 12.9, pp. 453-454
Problem 13.1, p. 485
Problem 13.6, p. 485

Instructor: Max Hailperin