-1

I have a problem with a SELECT with multiple inner joins. My code is as follows:

SELECT `movies02`.`id`, `movies02`.`title`,
       `movies03`.`talent`, 
       `movies07`.`character`,
       `movies05`.`genre`
  FROM `movies02`
 INNER JOIN `movies07` ON `movies07`.`movie` = `movies02`.`id`
 INNER JOIN `movies03` ON `movies03`.`id` = `movies07`.`performer`
 INNER JOIN `movies08` ON `movies08`.`genre` = `movies05`.`id`
 INNER JOIN `movies02` ON `movies08`.`movie` = `movies02`.`id`;

Doing an INNER JOIN to get the actors in the movie, as well as the characters they play, seems to work but the second two, which get the movie genre, don't work so I figure I can just write them as a VIEW and then combine them when I output the results. I would, therefore, end up with three VIEWs. One to get the genres, actors and characters, and then one to put everything together. Question is whether it is better to do that than one massive SELECT with multiple joins?

I tried rewriting the query a bunch of times and in multiple ways

O. Jones
  • 103,626
  • 17
  • 118
  • 172
wrichards853
  • 13
  • 1
  • Almost a duplicate of this old question: https://stackoverflow.com/q/7451688/20860, and the answers to that question are still applicable. – Bill Karwin Mar 25 '22 at 17:23

2 Answers2

1

When you do a query involving views, MySQL / MariaDB's query planner assembles all the views and your main query into a single query before working out how to access your tables. So, performance is roughly the same when using views, Common Table Expressions, and/or subqueries.

That being said, views are a useful way of encapsulating some query complexity.

And, you can grant a partly-trusted user access to a view without granting them access to the underlying tables.

The downside of views is the same as the downside of putting any application logic into your DBMS rather than in your application: it's trickier to update, and easier to forget to update. (This isn't relevant if you have a solid application-update workflow that updates views, stored functions, and stored procedures as it updates your application code.)

That being said, a good way to write queries like this is to start with the table containing the "top-level" entity. In your case I think it's the movie. Then LEFT JOIN the other tables rather than INNER JOINing them. That way you'll still see the movie in your results even when some of its subsidiary entities (performer, genre, I guess) are missing.

Pro tip: If you can, name your tables for the entities they contain (movie, genre, actor, etc) rather than using names like whatever01, whatever02 ... It's really important to be able to look at queries and reason about them, and naming the tables makes that easier.

O. Jones
  • 103,626
  • 17
  • 118
  • 172
  • Kudos for the last paragraph. I see countless teams that forget to version the views definitions in the source code when views are deployed/updated to production. And then, months/years later no one knows which version is running in production. – The Impaler Mar 25 '22 at 18:18
0

Views are just sintactic sugar for queries. When you include a view in a query the engine reads the definition of it and combines it in the query.

They are useful to make queries easier to read and to type.

On the flip side, they can be detrimental to the query performance when naïve developers use them indiscriminately and end up producing queries that become unnecessarily complex behind the scenes. Use them with care.

Now, materialized view are a totally different story since they are pre-computed and refreshed at specific times or events. They can be quite fast to use since they can be indexed, but on the flip side their refresh interval configuration mean they may be showing data that is not 100% up to date.

The Impaler
  • 45,731
  • 9
  • 39
  • 76