Database view usage and impact on performance of website

The word View itself suggests that it will be restricted access to some information in database based on privileges and shared summary of information from different tables.

A view is a virtual table.They are just complex query to create abstraction for user .These are not real table because it does not take physical memory more it is like table on fly .View may consists data from many table .
It may be required to show to user on a page from many table with just querying view than dealing with complex queries .

Most of time view are created to simplify interaction with DB and to make faster retrieval of group of data.

Advantages -

1. Grouping data from multiple table in one virtual table
2.Hiding complexity of query
3.They may provide security depending on DB privileges.For example you can create view for admins to see all other information except password .
4. It saves memory as they are not real tables .They are just special group of queries
5. If data in real table is updated then it will reflect in the view .
Suppose very simple a situation there is "employee" with emp_id(pk) and emp_name columns .There is other table with "department" id_emp(fk) , dept_name .
You want to show table in UI with employee name and Department name .Create view for this like view_emp_dept and query that for showing or updating data.



Examples - create view

CREATE OR REPLACE  VIEW view_emp_dept AS
SELECT emp_id ,emp_name , dept_name
FROM employee, department
GROUP BY name

Now query this view to get employee

Example - Get data from view

 SELECT emp_name , dept_name from  view_emp_dept  where emp_id =10;

Example - drop view

 DROP VIEW view_name view_emp_dept

If view are not defined properly then we have to create new view for each situation so define views considering some generic situation .
If view definition is not efficient for example distinct key word, unrefereed tables etc it can kill performance so before defining view we have consider time taken by that query .


So judicious use of view could help to simplify queries in code and could make our life simpler .