Are MySQL views more efficient than complex queries?

Question

I have a problem with SELECT statements using 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`;

Using an INNER JOIN to get the actors in the movie and the roles they played seems to work, but the last two joins to get the movie type don't work, so I thought I could write them into a view and then when outputting the results put They are combined. So I end up with three views. One to get the genre, actors, and roles, and then one to put everything together. The question is, is this better than using one large SELECT statement and multiple connections?

I tried rewriting the query multiple times and tried it in many ways

Answer 1

When you execute a query involving views, MySQL/MariaDB's query planner combines all views and the main query into a single query before determining how to access the table. Therefore, performance is essentially the same when using views, public expressions , and/or subqueries.

In other words, views are a useful way to encapsulate some query complexity.

Also, you can grant access to views to partially trusted users without granting them access to the underlying tables.

The disadvantages of views are the same as those of putting any application logic into the database management system instead of the application: updates are trickier and easier to forget to update. (This question is not relevant if you have a reliable application update workflow that updates views, stored functions, and stored procedures when application code is updated.)

That said, a good way to write this type of query is to start with the table containing the "top-level" entities. In your case, I think it's the movie. Then use LEFT JOIN to join other tables instead of using INNER JOIN. This way, even if some sub-entities (actor, genre, etc.) are missing, you can still see the movie in the results.

Pro Tip: If possible, name the table for the entities it contains (movie, genre, actor, etc.) instead of using whatever01, whatever02 The name of the class. It's important to be able to look at the query and reason about it, and table names can make this easier.