Home

A06: IMDB indexes

This assignment helps you practice with indexes and performance analysis. We will use data from IMDB, imported using the imdb-to-sql project. The data is in two separate databases: cinf201_imdb and cinf201_imdb_noindex.

Note, because this assignment involves benchmarks, which are somewhat nondeterministic, there are no automated test cases or testing script.

Tasks

In a single file named A06.sql, write queries that perform the following tasks:

  1. Switch to the cinf_imdb database. Execute a query that lists all indexes on the actors table.
  2. Switch to the cinf_imdb_noindex database. Execute the same query as in (1).
  3. Noting the difference between the actors table in both databases, execute a benchmark query that demonstrates the value of one or more indexes on the actors table. Perform the benchmark query on both databases.
  4. Build another benchmark query that uses a join across actors, acted_in, and movies tables. Again, try to highlight the benefits of indexes. Run the benchmark query on both databases.
  5. Build a query in which the “explain” feature of MySQL clearly shows that the query uses indexes in the indexed database (cinf201_imdb) but not the other (cinf201_imdb_noindex). Include this “explain” query in your SQL file, and run it on both databases.

Tester

As noted above, because this assignment involves benchmarks, which are somewhat nondeterministic, there are no automated test cases or testing script.

Submission

cinf201-submit A06

Hints

Recall that a benchmark query can only run “select” queries that return one result (one column value from one row). You might want to use aggregation to return a single value.

CINF 201 material by Joshua Eckroth is licensed under a Creative Commons Attribution-ShareAlike 3.0 Unported License. Source code for this website available at GitHub.