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.
In a single file named
A06.sql, write queries that perform the following tasks:
- Switch to the cinf_imdb database. Execute a query that lists all indexes on the
- Switch to the cinf_imdb_noindex database. Execute the same query as in (1).
- 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.
- 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.
- 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.
As noted above, because this assignment involves benchmarks, which are somewhat nondeterministic, there are no automated test cases or testing script.
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.