A SQL story in 2 parts - Are SQL views always good and how can I solve this example?

Question

Hiya,

I'm building a reporting app, and so I'm crunching an awful lot of data. Part of my approach to creating the app in an agile way is to use SQL views to take the strain off the DB if multiple users are all bashing away.

One example is:

    mysql_query("CREATE VIEW view_silverpop_clicks_baby_$email AS SELECT view_email_baby_position.EmailAddress, view_email_baby_position.days, silverpop_campaign_emails.id, silverpop_actions.`Click Name` , silverpop_actions.`Mailing Id`
FROM silverpop_actions
INNER JOIN view_email_baby_position ON (silverpop_actions.Email = view_email_baby_position.EmailAddress ) , silverpop_campaign_emails
WHERE silverpop_campaign_emails.id = $email
AND view_email_baby_position.days
BETWEEN silverpop_campaign_emails.low
AND silverpop_campaign_emails.high
AND silverpop_actions.`Event Type` = 'Click Through'") or die(mysql_error());

And then later in the script this view is used to calculate the number of clicks a particular flavour of this email has had.

    $sql = "SELECT count(*) as count FROM `view_silverpop_clicks_baby_$email` WHERE `Click Name` LIKE '$countme%'";

My question is in 2 parts really:

1. Are views always good? Can you have too many?
2. Could I create yet another set of views to cache the count variable in the second snippet of code. If so how could I approach this? I can't quite make this out yet.

Thanks!

Answer 1

To answer your questions.

1.) I don't know that I can think of an instance where views are BAD in and of themselves, but it would be bad to use them unnecessarily. Whether you can have too many really depends on your situation.

2.) Having another set of views will not cache the count variable so it wouldn't be beneficial from that standpoint.

Having said that, I think you have a misunderstanding on what a view actually does. A view is just a definition of a particular SQL statement and it does not cache data. When you execute a SELECT * FROM myView;, the database is still executing the select statement defined in the CREATE VIEW definition just as it would if a user was executing that statement.

Some database vendors offer a different kind of view called a materialized view. In this case the table data needed to create the view is stored/cached and is usually updated based on a refresh rate specified when you create it. This is "heavy" in the sense that your data is stored twice, but can create better execution plans because the data is already joined, aggregated, etc. Note though, you only see the data based on the last refresh of the materialized view, where with a normal view you see the data as it currently exists in the underlying tables. Currently, MySQL does not support materialized views.

Some useful uses of views are to:

• Create easier/cleaner SQL statements for complex queries (which is something you are doing)

• Security. If you have tables where you want a user to be able to see some columns or rows, but not other columns/rows, you restrict access to the base table and create a view of the base table that only selects the columns/rows that the user should have access too.

• Create aggregations of tables

Answer 2

Views are used by query optimizer so they often help in querying for information more efficiently.

Indexed or materialized views however create a table with the required information which can make quite a difference. Think of it as denormalization of you db scheme without changing existing scheme. You get best of both worlds.

1. Some views are never used so they represent needles compexity -which is bad.
2. Indexed views cannot reference other views (mssql) so there's hardly a point in creating such view.