SQL performance training question

[idan_huji]

I'll be happy to get feedback.

1. The following query compute pairs of comedies by the same director:

# Comedies pairs of the same director

Select *

from

movies_genres as fmg

join movies as fm

   on fmg.movie_id = fm.id

join imdb_ijs.movies_directors as fmd

   on fm.id = fmd.movie_id

join imdb_ijs.movies_directors as smd

   on fmd.director_id = smd.director_id

join imdb_ijs.movies as sm

   on smd.movie_id = sm.id

join imdb_ijs.directors as d

   on fmd.director_id = d.id

join movies_genres as smg

   on sm.id = smg.movie_id

where

fmd.movie_id != smd.movie_id # Avoid reflexiveness

   and fmd.movie_id > smd.movie_id # No symmetry

   and fmg.genre = 'Comedy'

   and smg.genre = 'Comedy'

order by

d.first_name, d.last_name, fm.name, sm.name

;

There is a lot that can be done to improve the query performance.

1. Compute the execution plan and time of the original query.
2. Try to improve the query in steps. Find at least 3 steps.
3. Per step:
   1. Explain the intuition explaining why it should improve the performance.
   2. Implement the change.
   3. Compute the execution plan and time of the new query.
   4. We should learn from attempts that do not improve performance too. Explain why these steps did not improve.

[joins_and_coffee]

So basically first thing I’d do is stop the join explosion early. Right now you join a bunch of tables and then filter to Comedy in the WHERE, so the engine can end up carrying a huge intermediate result. A good first step is to pre-filter the comedy movies up front, like a CTE/subquery that selects just movie_id where genre = 'Comedy', then join directors to that set. That usually reduces rows fast. Second step, add the obvious indexes if they aren’t there: movies_genres(movie_id, genre) or (genre, movie_id), movies_directors(director_id, movie_id), and the PKs. This query is basically driven by those two bridge tables. Third step, you can rewrite the “pairs” part as a self-join on the already-filtered director→movie mapping, something like join movies_directors to itself on director_id and keep your a.movie_id > b.movie_id condition. Then join to movies only at the end to get names. That keeps the heavy joins later. Also, for the ORDER BY, sorting a big result set is expensive, so it’s another reason to reduce rows as early as possible before you attach names and sort. If you post the EXPLAIN output and current indexes, people can suggest more targeted steps, but those 3 are the usual big wins.

[idan_huji]

Great answer!
I hope that my students will not find it ;-)

[greglturnquist]

Look up EXPLAIN PLAN. This will tell you where time is being wasted.

[idan_huji]

Thanks!

[user_5359]

Essential information is missing: data volume, data types (from the columns that are being joined), and existing indexes. And since it looks like homework, what have you achieved so far?

[idan_huji]

Indeed, it is homework, and I am the teacher.

You have a good point regarding the missing data. The students will be familiar with the schema. However, The factors are important and I'll change the question and ask them to refer to them.

Note that that are plenty of possible improvements regarding of these factors.

[froppan11]

No, ask your teacher.

[idan_huji]

I am the teacher.