MySQL Views

You have just written a new feature for your awesome application that searches through your database to find ‘x’ and you run some tests to check that the returned result is correct.

Unfortunately, your test fails because it returns more results than expected. At first this is not obvious because it’s been a while since you worked on the application, but eventually you realise that every search should be automatically filtered out by a boolean field.

You may have run into a similar situation where you or someone has forgotten to filter by a value in field x and it’s now potentially showing incorrect data, thereby damaging business. Fortunately, there are some tests in place to catch this.

Let’s work through an example. You have a user table and, with good reason, you are utilizing a boolean field to represent what the state of the user is, perhaps to determine if it is disabled. You could copy data from a “live table” to a “disabled table” but you feel the complexity and overhead to implement this is too high, because of other user related tables.

With the above scenario in mind, I think MySQL Views can improve on this solution. Views are quite simple things. They present a table based on the defined SELECT query. We can create a view that will only return users that are enabled in the system and which will prevent future features from including disabled users.

I’ll explain how to create this view and how to use it in your application. Something to note is MySQL views are only available from version 5.

Here is the basic user table the view will be based on:

CREATE  TABLE `users` (

`user_id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,

`first_name` VARCHAR(100) NULL ,

`last_name` VARCHAR(100) NULL ,

`username` VARCHAR(100) NULL ,

`dob` DATETIME NULL ,

`disabled` BIT NULL DEFAULT 0 ,

PRIMARY KEY (`user_id`) );

Below is the query that will create a MySQL view for the above user table.

CREATE OR REPLACE ALGORITHM = MERGE VIEW `v_users_enabled`

(`firstName`, `lastName`, `username`, `dob`)

AS

SELECT `first_name`, `last_name`, `username`, `dob`

FROM `users`

WHERE `disabled` = 1;

How does it work?

Here is a breakdown of the keywords used to create a view.

CREATE OR REPLACE

This creates a new view and is required. Optionally you can add OR REPLACE if you want to make certain it is created but if you know the view exists already you can use ALTER to make changes to an existing view.

ALGORITHM = MERGE

This is an optional statement and by not defining one or explicitly stating ALGORITHM = UNDEFINED, MySQL will choose between two options that best fit the SELECT statement, i.e. MERGE or TEMPTABLE. MySQL will try to choose MERGE over TEMPTABLE where possible because it is more efficient. I will discuss more about the algorithm after going through these core points.

VIEW

Is a required statement and is used to give a name to the view. Crucially a view cannot have the same name as a table because they share the same name space.

The column list part is optional and by default the column names in the SELECT statement are used. If you choose to define column names they are comma separated, must be unique and match the number of columns in the SELECT.

AS

Is required and where you define the SELECT query.

Querying a view is the same as when querying a table. Here’s a simple example:

SELECT * FROM v_users_enabled

More about the algorithm

When defining the algorithm, you have three options to choose from: MERGE, TEMPTABLE or UNDEFINED.

We now know that UNDEFINED lets MySQL choose the appropriate option, but what do the other two options mean?

MERGE is the fastest out of the two options. The view column list replaces what is in the SELECT statement, essentially merging faster than TEMPTABLE as that generates a new temporary table that is queried upon and which has no indexes. MySQL will warn you if you try to use MERGE when a TEMPTABLE should be used and will change it to TEMPTABLE.

When will the TEMPTABLE option be used? If you use any aggregation function, DISTINCT, LIMIT, GROUP BY, HAVING, sub query or literal values (i.e. no table).

Minor performance trade off

Unfortunately, MySQL views will hinder performance rather than improve it. It is important to think about your views and to use MERGE where possible to minimise performance reduction.

Provided your performance budgets can take the hit to better manage complexity and create a useful separation this, in my view, is a worthy trade off because you are taking a query with certain where clauses that may need to be applied to other queries. This is nicely contained within a view and database level users can query against this returning them the correct results, without them having to remember they need these additional where clauses.

The downside of this containment is that it would be possible to write a complex query within the view which is now hidden. Queries with more where clauses on this view will make the overall query a monster and potentially inefficient. A judgement call will need to be made on how complex the view SELECT query can be without compromising performance.

To better optimize for performance when using a view, use the MERGE algorithm and when creating your index add the fields in your WHERE clauses that exist in the view first. It is important they are put in the correct order, as usual when creating indexes for expected queries. You can use EXPLAIN to check your query and make sure its behaving as you expect.

Benefits and Negatives

The benefits of using a view:

• Provide a useful data separation from application by containing the need to remember specific fields the query needs to filter by for all SELECT statements within the database.
• Tables can change over time and you need to add some new fields that you intend to filter by for most SELECT queries. All that needs to change is the SELECT statement with the new fields after altering the tables. Then updating the application will be easier as you may only need to remember to change it in a few places.
• They can make your SELECT queries more readable.

The negatives of using a view:

• When the TEMPTABLE algorithm is used no index is used.
• They could hide a complex query and with querying the view could turn it into a sluggish query.
• Using the MERGE algorithm limits your view SELECT statement to basic querying only.
• You may choose the MERGE algorithm but if MySQL thinks it should use TEMPTABLE then it will change it.
• If your view does not have a one-to-one relationship with the table then it is not updatable.
• When you do add or change a table you will still need to update the CUD statements in your application.
• You cannot associate a trigger with a view.

Noteworthy quirks

If you want to use ORDER BY in your view SELECT statement it is worth noting that this cannot be overridden when you query the view using a different order.

If you decide to put a LIMIT in your view and then use another LIMIT when you query the view it is undefined which LIMIT applies.

If you are interested in making a view updatable see MySQLwebsite.

Conclusion

MySQL views are a useful tool and provide a good solution in particular scenarios. It is clear that views have their limits and it is important to be aware of them. Used in the right way your next app can benefit from flexible containment and control over how the data is accessed.