Level 63
Some documentations explain that you have to use views when the queries becomes to complex with more than 2 nested relationships.
Dec 21, 2024
8
Level 1
When Should You Create Views in Your Database?
Creating views in a database can offer significant performance improvements and simplifications, particularly when dealing with complex queries or aggregations. In this article, we'll explore when to use views and materialized views in your database, why they are useful, and how they can optimize your system's performance for faster results.
What are Database Views? A view in a database is a stored query that can be treated like a table. Views are not physical but virtual tables that provide an abstraction layer over your database schema. The query underlying a view is executed each time the view is queried, simplifying complex queries or aggregations.
Materialized views are similar but differ in that they store the query results physically, updating periodically or on-demand, which can lead to significant performance benefits.
When to Create Views in the Database
- Complex Joins and Aggregations
If your queries involve multiple tables with complex joins and aggregations, creating a view can simplify the query structure and improve performance. For example, joining data from articles, authors, and tags can become tedious if repeated in every query. Instead, you can create a view that encapsulates the complex logic. read more at
https://dev.to/arkdevsolutions/when-should-you-create-views-in-your-database-52hb
Level 74
@vincent15000 Not entierly true. Views are commonly used to make you not have to repeat yourself so much. Let say that you in several places uses a particular join between two tables, then you would benefit from creating a view instead, there more complex the query gets the better it is to use a view.
You can also use it to make more fields searchable, or just to make your Eloquent, well stay eloquent.
I wrote a post about it a couple of years ago.
https://tray2.se/posts/use-a-view-instead-of-a-complex-eloquent-query-in-your-laravel-application
2 likes
Level 7
@Tray2 What happens when you delete or update any record? Should you update the view and how?
2 likes
Level 5
@Merklin The same question came to my mind too, or rather what is the difference between a view and an aggregate table? And the answer looks like:
"views are definitions built on top of other tables (or views), and do not hold data themselves. If data is changing in the underlying table, the same change is reflected in the view."
1 like
Level 74
@Merklin and @telexx The view is basically a query stored in the database, so it doesn't hold any data, it runs the query kinda like a subquery.
The select from the view would look something like this in a very simplified way
SELECT * FROM my_nice_view;
And what the database sees is something along the lines of
SELECT * FROM (SELECT column1, column2) FROM my_nice_table WHERE column1 = 1);
Just a fun fact, the second implementation with a select in the from clause, is called an inline view.
1 like
Level 122
I used a view recently to good effect, but was surprised to learn that I could not add an index
1 like
Level 74
@Snapey That is correct, you need to have the needed indexes on your original tables, or use a federated view, but I don't recommend that unless you fetch data from another database server, then it might be a good idea to fetch the data to a federated view.
2 likes
Level 1
Yes, that's correct. Ensuring the necessary indexes are present on your original tables is crucial for optimal performance. Proper indexing significantly improves query efficiency, especially for large datasets or frequently accessed tables.
If you're fetching data from another database server, using a federated view can be a viable option. A federated view allows you to access remote database tables as if they were part of your local database, providing real-time access to data across servers.
However, I would caution against using federated views unless you specifically need to pull data from an external database server. Federated views can introduce additional complexity and potential performance overhead, especially if the remote server experiences latency or downtime. For local data, leveraging indexes and optimizing queries are typically more efficient and reliable strategies
1 like
Please or to participate in this conversation.