One suboptimal database query can effectively paralyse whole application. Tracking down such bottleneck is one thing but speeding it up is not a trivial task. This article is intended to show how one can analyse and refactor slow database query.
DB's structure is illustrated here. Please open it and refer to before reading any assignment's SQL.
Let's say we want to have a...payment amount of a latest rental date per film. Query I wrote:
SELECT title, amount FROM ( SELECT film.title, payment.amount FROM film JOIN inventory ON inventory.film_id = film.film_id JOIN rental ON rental.inventory_id = inventory.inventory_id JOIN payment ON payment.rental_id = rental.rental_id ORDER BY film.title, rental.rental_id DESC, amount DESC ) AS f GROUP BY title
On the bright side it shows all relations between tables in a very readable, straightforward way. Too bad it is suboptimal. I could easily support this claim using MySQL's EXPLAIN, but MySQL Workbench has a very useful feature of presenting EXPLAIN's results in a graphical form:
One should read this diagram starting from the lower-left corner. This query is actually very poor when it comes to efficiency. Despite the fact it utilizes indexes JOINing another tables, in the end it has to perform grouping operation and reject almost all results. This stage (rejecting) is depicted as "Full Table Scan" rectangle. This will become even more problematic over time because our tables will grow, not shrink. Especially rentals (history) table.
Let's consider another approach to the same problem:
SELECT film.title, payment.amount FROM film JOIN rental ON rental.rental_id = ( SELECT max(rental_id) FROM rental WHERE inventory_id IN ( SELECT inventory_id FROM inventory WHERE film_id = film.film_id ) ) JOIN payment ON payment.rental_id = rental.rental_id ORDER BY amount DESC
It may look complicated owing to usage of a dependant subquery. So, what is it anyway? Using example is the best way to describe it. Going step by step, we need some columns from film and payment tables. However, there is no way to JOIN them directly and this is why we need rental table for. Unfortunately, there is no straightforward way to JOIN it either.
SELECT film.title, payment.amount FROM film JOIN rental ON rental.rental_id = -- ? JOIN payment ON payment.rental_id = rental.rental_id
Rentals are associated with inventory table rows by a relation called many-to-one. In other words, there may be multiple rentals of the same inventory item which is directly associated with a film (finally). So in order to reach a latest rental I wrote a subquery:
SELECT max(rental_id) FROM rental WHERE inventory_id IN ( SELECT inventory_id FROM inventory WHERE film_id = film.film_id -- <-------- DEPENDENCE! )
It is dependant on the outer query because to calculate outcome it needs a film_id. Therefore, DB engine has to execute it for each row from film table. It may sound as if it was an inefficient solution. Let's dispute this claim using MySQL Workbench:
As you can see, it's not completely bad after all. We see that there are multiple indexes usages, so despite the fact we execute subquery per each row from film table, we still get latest rental quite efficiently.
On a side note - I can't see the reason why MySQL engine used Full Index Scan on the film table. It makes no sense because query returns all rows from film table anyway - I did not introduce any kind of filtering.
Resultant query is quite satisfactory. There is no waste - no rows are rejected at all. It also leaves a room for easy filtering film table.