Optimize SQL databases – Views

2011-02-27

Views are virtual tables that, for the database or the user, look like ordinary tables. But in reality, they are just queries on other tables or views. There are several reasons to use views:

You can also create a separate table for complex and resource demanding queries. It can be useful for complex queries that are frequently asked. This is also known as a materialized view or a snapshot. It is not updated automatically, but can be updated from procedures or by the application. A materialized view can also be seen as a cache that stores the result of a query. It therefore needs to be up to date.

In this example, a view is created that shows how many posts each user has written. As previously said, a view is created from a query.

CREATE OR REPLACE VIEW Posts_per_user AS
SELECT Users.ID AS ID, Users.username AS username, COUNT(Posts.id) AS count
FROM Users LEFT JOIN Posts ON Users.ID = Posts.author GROUP BY Users.ID;

Other articles in this series: