An Introduction to MySQL 5: Views

The MySQL 5 series introduced quite a few changes. Triggers and stored procedures were two of the big ticket items. One of the lesser known additions, at least from the amount of writing on the subject, is the introduction of views. While after a quick look at MySQL views, you might not see the obvious advantages, they’re there if you dig into them just a bit.

Introduction: What is a View

“A View is nothing more than a pseudo table doing the work of a defined query.”

In short, the easiest way to look at a view is that it is just a stored query which mimics a table. It’s nothing more than a pseudo table doing the work of a defined query. It doesn’t add any functionality, such as changing variables, nor does it fire other queries when something else happens. Views just sit there, fat dumb and happy doing what you defined them to do.

On first blush, this doesn’t sound like that big of a deal, but if you dig past the surface, you can start seeing some of the power of the lowly view. I am not going to say that views will change your life, but I will tell you that they will make the job of database interaction a little easier to work with. They will make your job a little easier when you make major version changes in your interaction layer. They will also make some difficult tasks such as dynamic reporting a little more efficient and a little easier to work with. I’ll take a little easier any day of the week.

With anything there are tradeoffs.

“Views may and probably will decrease your performance.”

As I have written in the past, I am a believer in making tradeoffs, as long as you understand what is on the table. More than likely someone will skim past this paragraph and make a comment that you should never use a view because of the performance hit. I disagree. You should use every tool in your toolbox, but when they make sense. You don’t use a hammer to put a screw into a wall, just as you wouldn’t use a view when you really need a heap / memory table. The flip side is the development usability for your applications. When it makes sense to save time, effort, and energy over the performance hit that you might take, take that choice. Development isn’t all about the performance of your applications, as there are other considerations such as support, time to market, and overall value of your time.

The tools that I am working with in this tutorial are pretty standard. I am using phpMyAdmin for the database interaction for explanation purposes. I will also be using very rudimentary table structures, strictly for ease of explanation. I don’t expect that these table structures would ever be used in a production environment, as I am merely using them for illustration.

One further note. There is no right or wrong way to name a view. However, I do name my views with the syntax of view_*primary_table*_*what_the_view_is_used_for* unless I am working for backwards compatibility changes. So, if I was creating a view for statistical reporting purposes on my salesforce table, my view name would be: view_salesforce_statistical_report. That can be rather long and you only have 64 characters to work with, so keep that in mind. It works for me, and my style, it might not work for you.

“I am not going to say that views will change your life, but I will tell you that they will make the job of database interaction a little easier to work with. They will make your job a little easier when you make major version changes in your interaction layer. They will also make some difficult tasks such as dynamic reporting a little more efficient and a little easier to work with.”

Definitions: How to Define a View

As I stated, a view is just a query. There are some slight configurations that we need to make when creating a view, so let’s discuss that first. In phpMyAdmin, in the “browse” table page, you will see a link called “Create View”.

If you click on that link you should see something that looks like this:

This, my friends, is where the magic happens. There isn’t much to explain on the page, but let’s take a quick look at the definitions that we need to understand to create a view.

First, there is “Create View” followed by “OR REPLACE”. If you click the OR REPLACE it will do exactly as you think, which is overwrite an existing view. Column names is the name of the columns in your table. Each is seperated by a comma, so it might look like first_name, second_name, etc. AS is the query.

There are two more items to explain, but the concepts are not hard. ALGORITHM has the selections of undefined, merge, and temp table. If you select “Merge” when there is a one to one relationship it will combine the incoming query with the view. Temp table is less efficient, but when you are not using a one to one relationship, such as a aggregation function like SUM() or you are using certain keywords like GROUP BY or HAVING or UNION, you have to use the temp table algorithm. That said, you can do like I do, and leave the algorithm as “undefined” and MySQL will select the best algorithm to use.

Finally, we have CASCADED CHECK OPTION and LOCAL CHECK options. The check options tell MySQL to validate the view definition if there is a condition on the query, like WHERE. If the WHERE clause excludes data, it will prevent updates or insertion of those rows where it should be excluded. The LOCAL CHECK deals with just the view you are defining, where CASCADED CHECK is for views that you have defined from other views. It will cascade the check for those as well.

That’s a view in a nutshell. Let’s take a look at some use cases to see them in action and where they may help your development efforts.

Backward Compatibility: For the Procrastinator

I have had it happen more times than I would care to mention when I design a single use table which I never think will need to be normalized further inevitably does. Let’s take the example that showed before with a few more records.

Obviously, my normalization skills leave something to be desired in this example. What I probably should have done when I first created this table, was have a seperate table for addresses, and then just call an address_id in my sales force table. The problem is, once I move to a database change, I have to run back through my logical interaction layer and make numerous query changes. Instead of doing that much work, why not let a view come to the rescue.

First, let’s make the change to my table structure. I copy my table structure and data to my new table, addresses and make the table sane such as adding address_id and removing the unneeded structure:

Then I just need to delete the offending columns and add my address_id back to my sales table.

This is a pretty common change that you make on a semi-regular basis, although rather simplistic in nature. You figure out that you can normalize something because of a new feature. In this case, we can reuse our addresses for customers, or for other employees, or for whatever we might store addresses. This work isn’t that difficult, but depending on your query reuse, finding all of the places that you call our old sales_force table might be a much larger change in scope. In comes a view.

Instead of going back through our code right now, and instead wait for a normal release cycle, we can create a view to keep our old functionality intact. I changed the name of our sales_force table to sales_force_normalized:

Now we can create our view to maintain backwards compatibility:

And we have our backwards compatibility with just the extra work of creating one query that sits in MySQL:

Even when I enter a new sales person, my view will reflect the change:

And, presto:

About two minutes of work to maintain our backwards compatibility to our previous data structure. There are drawbacks to this method, in that you can not define an index against your view which is important when you are cascading views. In addition, you will still need to change your queries for INSERT, DELETE and UPDATE, but this will save you some work. Your performance could drop a bit, but as a stop gap, there is no easier way to make a change to your data structure to ease your code base into that change. Your queries in your logic layer will be untouched because as far as they know, they are looking at the original table.

Complex Queries: Making the Hard Bearable

Now that we have our proof of concept under our belts, let’s look at another use. I created another table to capture the sales data from my salesforce table and filled it with some random information. It looks like this:

It’s an extremely simplified table to capture the sales of the salesforce for illustration. There are always things that we want to extract for measurement on a table like this. I probably want to know the total sales. I probably would want to know the total sales by person. I also might want to know the rank of the sales performance. I could write queries in my database logic to perform each of these when I need them, or I could simply write a view to grab the data as needed. Since this is a tutorial about views, I guess the choice is pretty simple at this point which tactic to take.

Let’s start by evaluating the total sales, along with some other pertinent information:

Which gives us a view of:

I also included the query time on this one, as looking at 200 records, this is lightening fast, but performance will vary. Notice that I am not using the CHECK functions because I am not discriminating the information in a WHERE clause. Now that we have this information neatly packaged, it’s just a matter of building our reporting mechanism in our logic.

Getting this information isn’t that hard. Let’s take this just a step further and use a GROUP BY function and a join function against the salesforce. Again, I am using simplified queries to illustrate. In this case, we want to get the same information that we had from total sales, but this time broken down by our individual sales person.

Which gives us a view of:

Again, very simple in the end to get these values out of your database. Let’s take a look at one more example, which will combine the two views. I want to compare the totals against the individual, and so we will create a view of two views:

Which gives us a view of:

Conclusion

One other benefit of views is they do provide a further level of security to your applications. You are not exposing your table structure to your application. Instead, you’re exposing something that doesn’t really exist, except as a pseudo table. I wouldn’t call a view a best practice and use them primarily for writing secure applications, but I would look at it as an added benefit.

I use views in a limited fashion. Where I use views are as demonstrated above, particularly in the reporting mechanisms in my applications. Writing a query to perform the heavy lifting for me is much easier than writing the logic around more difficult queries. I do take a bit of a hit on my performance from time to time, which I tend to overcome by optimizing the original data structure. I’ve yet to have one be a show stopper in terms of performance, but the day is young. Thanks so much for reading.