Using Views to Boost Performance

MySQL implemented views beginning with version 5.0. Most database administrators and developers don't normally think of views as a performance enhancer, but these database structures can add tremendous value to your applications. This section explores how you can take advantage of views to boost responsiveness.

Reduced Returned Columns

Retrieving information via SELECT * is second nature for most database administrators and developers, and is often baked in to many general-purpose data access products. Unfortunately, this often returns extra columns that are not of primary interest at the moment. When these kinds of queries are run en masse, it can negatively affect performance by increasing engine load, as well as consuming extra bandwidth between the client and database server. In situations like this, it's a better idea to create a view that returns only those columns that are absolutely necessary for the current task.

For example, suppose that one small part of High-Hat Airways' web-based client portal lets customers view and update their mailing address from the customer_master table. Given the millions of High-Hat customers, it's likely that this operation will be run thousands of times per week. You could construct your SQL statement as follows:

 SELECT * FROM customer_master WHERE...  

and then ignore any columns that aren't related to the customer's address. However, this extracts and sends unnecessary columns to the requesting application. You could also modify your SQL statement to something similar to the following:

 SELECT address1, address2, city, province, postal_code... FROM customer_master WHERE... 

but this is somewhat tedious and potentially error-prone. In this case, it's probably worth the effort to construct a view that just returns the right columns for the job:

 CREATE VIEW V_CUSTOMER_ADDRESS AS SELECT address1, address2, ... FROM customer_master  WHERE... WITH CHECK OPTION 

Adding the WITH CHECK OPTION at the end of the statement forces updates to the view to undergo validity checks before being accepted into the database. This is discussed in more detail momentarily.

Reduced Returned Rows

Views are also great for reducing the number of potential returned rows when performing a query. Many users (especially those using end-user reporting tools) issue blanket queries that result in sequential reads through very large tables. This is very inefficient and possibly dangerous for performance.

To help reduce the prospect of these kinds of unnecessary gargantuan data sets, you can employ views with more restrictive WHERE clauses. For example, suppose that users in High Hat's marketing department want to use a business intelligence tool to research the traveling habits of the most frequent customers who normally fly from Chicago's O'Hare airport. You could allow them unrestricted access to the appropriate tables. However, this is a recipe for disaster: It's quite likely that the users will construct extremely wasteful queries, no matter how advanced the business intelligence tool.

Instead, it is smarter to create a customized view:

 CREATE VIEW V_FREQUENT_TRAVELERS_OHARE AS SELECT first_name, last_name, ... FROM customer_master, flight_history,.. WHERE customer_master.account_balance > 50000  AND flight_history.departure_airport = 'ORD' 

By directing your users at this view, you're much less likely to encounter performance-draining open queries.

Reduced Inefficient Joins

As a database administrator, you know that it's often difficult to construct efficient queries, even with your expertise in SQL. It's much harder for untrained developers and end users (with direct or indirect SQL access). These people often inadvertently submit horrendous queries that might overwhelm your MySQL server.

This problem is even more troubling when you realize that many queries invoke joins between two or more tables, which can cause performance to become exponentially worse. Fortunately, views provide you with defense against these kinds of issues.

If you have the authority, you can create a set of views that is designed with efficiency in mind. By understanding your users' needs, consulting the results of MySQL's EXPLAIN command, and experimenting, it's likely that you'll design views that are as efficient as possible. You can then require that end users and developers work through your views, rather than against the base tables.

Simpler Updating with Integrity Checking

MySQL 5.0 introduces updateable views. These can be very handy when developing applications because they let you update data in the same location as you found it (that is, you don't need to update base tables when you just found the information in the view).

In addition, from version 5.0.2 onward, appending WITH CHECK OPTION to your view creation statement enforces database engine validation of data modifications.

As shown earlier in this chapter, for example, the High-Hat Airways customer_master table serves as the foundation of all customer information. Suppose that you create a view to return the names and club status of only those lucky travelers who have reached the pinnacle of their Mad-Hatter Club (High-Hat Airways' award-winning frequent flyer program) membership:

 CREATE VIEW V_HATTER_CLUB_BEST AS SELECT first_name, last_name, club_level, home_airport FROM customer_master WHERE club_level = 'Top-Hat' WITH CHECK OPTION 

Next, you want to create an additional view that returns a subset of the first view. In particular, you only want to see those customers who have indicated JFK as their home airport:

 CREATE VIEW V_HATTER_CLUB_BEST_JFK AS SELECT first_name, last_name, club_level, home_airport FROM V_HATTER_CLUB_BEST WHERE home_airport = 'JFK' WITH CASCADED CHECK OPTION 

If a user attempts to insert a record into the V_HATTER_CLUB_BEST_JFK table and does not assign a club_level of 'Top-Hat', MySQL returns an error message (and blocks the insert) because this violates the rules that created the underlying V_HATTER_CLUB_BEST. This is a very simple example, but the concept is very powerful. Updateable views combined with cascaded checks can reduce the amount of validation that you must perform in your programs, while helping increase data integrity. Constraints are discussed in more detail in Chapter 5.