How do views work in DBM?
Let's say that I have two tables:
Employers (id, name, ...., deptId). Depts (id, deptName, ...).
But this data won't change that often and I want a query like this
SELECT name, deptName FROM Employers, Depts
WHERE deptId = Depts.id AND Employers.id="ID"
be as fast as possible.
Two possible solutions come to mind:
-
Denormalize the table:
Even though with this solution I will lose some of the benefits that "normalized databases" have, but here performance is a MUST.
-
Create a view for Denormalize data.
I'll keep Data Normalized and ( here's my question ) the query performance over this view will be faster than without this view.
Or another way to ask the same question, the view is "interpreted" every time you query it, or how the Things in DBA view works.
source to share
In general, unless you "materialize" a view, which is a variant in some software like MS SQL Server, the view is simply converted to queries on the underlying tables and is therefore not faster or slower than the original (minus the minimum amount the time it takes to translate the request, which is nothing compared to actually executing the request).
How do you know you are having performance issues? Do you profile it under load? Have you confirmed that these two tables are the performance bottleneck? Generally, unless you have hard data, don't assume you know where the performance problems are coming from, and don't waste time optimizing until you know you are optimizing the right thing - 80% of performance problems come from 20% of the code ...
source to share
If Depts.ID is the primary key of this table and you are indexing the Employers.DeptID field, this query should remain very fast even over millions of records.
Denormalization doesn't make sense to me in this scenario.
In general, the performance of the view will be almost the same as the performance when running the query itself. The advantage of the view is to just distract that request, so you don't have to think about it.
You can use a materialized view (or "snapshot" as some say), but then your data will be as fresh as the last update.
source to share
In a comment to one of the answers, the author of the question explains that he is looking for a way to create a materialized view in MySQL.
MySQL doesn't include the concept of materialized views in a nice package for you like other DBMSs, but it has all the tools you need to create one.
What you need to do is:
- Create an initial materialization of the result of your query.
- Create a trigger to insert into the employers table that inserts into the materialized table all rows that match the newly entered employer.
- Create a delete trigger on the employers table that deletes the matching rows from the materialized table.
- Create an update trigger on the employers table that updates the corresponding rows in the materialized table.
- The same goes for the department table.
This may work fine if your base tables are not updated frequently; but you need to know the additional cost of create / update / delete operations once you do. Also you will want to make sure that some DBA who is unaware of your trickery does not migrate the database without migrating triggers when the time comes. Document so well.
source to share
Sounds like a premature optimization if you don't know that this is a clear and real problem.
MySQL does not implement views, they are not faster than queries against underlying tables. Moreover, in some cases they are slower because they optimize less well.
But views also "hide" things from developers who maintain the code in the future to make them imagine that the query is less complex than it actually is.
source to share