Is creating a view of multiple joining table faster then gettin that data directly using the join in a query?

Question

Following is the query which is hit every table has over 100,000 records.

   SELECT b.login as userEmail, imgateway_instance_id as img, u.id as userId 
   FROM  buddy b
   INNER JOIN `user` u ON b.username = u.login
    INNER JOIN bot_to_buddy btb ON b.id = btb.buddy_id
    INNER JOIN bot ON btb.bot_id = bot.id
    WHERE u.id IN 14242

Answer 1

No, you cannot gain performance from using a view. Behind the scene, your original query is run when you query the view.

Answer 2

Sometimes using views can gain a small bit of performance, like it says in High Performance MySQL

On the other hand the author of the book has written this blog: Views as performance trouble maker

Answer 3

Generally speaking, this depends on how you submit your query.

The view MAY be faster:

For example, in PHP it's common practice to submit the query "dynamically" (i.e. NOT as an prepared statement). That means MySQL has to compile the query every time you call it. When using a view, this in done once when the view is created.

Regarding MySQL as an DBMS, I heard about performance issues with Views in earlier versions. (Don't know what the current situation is, though).

As a general rule in such questions, just benchmark your query to get real life results. Looks like you have already populated your database with a lot of data, so this should yield meaningful results. (Don't forget to disable caching in MySQL).

Answer 4

There's little reason having a view run your query instead of running the query yourself be any faster with MySQL.

Views in MySQL is generally so poorly implemented, we had to back out of using them for many of our projects.

Check with EXPLAIN what your query does when you place it in a view, looking at that query, it can probably still use the proper indexes even it's part of a view, so it'll atleast not be slower.

Answer 5

Using joins with tables who have a large amount of records as yours is often very slow as joins will go over every record in a table which makes the query take a lot of time.

As a personally experienced solution i would suggest you to try and cut down the results of your query by using WHERE as mush as you can and then use joins.