Using derived expressions in MySQL views

Question

The examples here show a very simplified version of a VIEW that I am constructing in MySQL. The goal here is to create a view from which I can select a single set of results to be populated into a HTML table.

The purpose of me using a view is, in part, to show calculations over this data as columns in the view. I can do this easily by giving aliases to expressions, such as: SELECT a, b, a + b AS c will give me a resultset with three columns, of which the values in column c are the addition of values in columns a and b

Example 1

This is how I am currently constructing a VIEW over my fictional "product stock" table:

SELECT
  description,
  unit_cost,
  current_stock,
  required_stock,

  (required_stock - current_stock)                     AS stock_shortfall,
  (required_stock - current_stock) * unit_cost         AS stock_shortfall_cost
FROM product_stock

It is important to me to be able to show both the stock shortfall, and the cost of replacing that stock in the table. You can see that this isn't fully optimised, as part of the stock shortfall calculation is used again to calculate the cost.

This also means that if something were to change in how the calculation of the stock shortfall was made, the same change would need to be made to the stock shortfall cost calculation, increasing the difficulty of maintaining the code.

Example 2 (Desirable Solution)

I can create a SELECT statement like this which will allow me to reference past expressions using user variables:

SELECT
  description,
  unit_cost,
  current_stock,
  required_stock,

  @stock_shortfall := (required_stock - current_stock)  AS stock_shortfall,
  @stock_shortfall * unit_cost                          AS stock_shortfall_cost
FROM product_stock

I can also achieve a similar effect by using sub-queries on derived tables by referring to the alias of the expression in further calculations.

Unfortunately, MySQL will not allow me to use user variables or derived table sub-queries within a VIEW.

Another solution could be to create a view in place of a derived table, and for each reused expression, create another view "layer" to select the derived value from. This works, but after so many layers, it also becomes unmaintainable.

I am currently performing a mixture of "layered" views and duplicate calculations like in Example #1. Please understand that the actual VIEW I wish to construct is a JOIN across 8 tables, with a lot of calculated values being reused in further expressions throughout. It is not very manageable.

Thanks for reading my wall of text. Here are my questions:

Is it possible to create a VIEW using derived expressions in a similar way to example #2? If not, how would you solve this problem?

What is the optimum solution? Should I be using a server-side script to calculate the values instead of using the database? Is there another database technology which does support this feature? What would you do?

Answer 1

We're basically doing the same thing where we work -> except with BLOBS, meaning every derived / temp table is automatically written to disk.

This is an absolute performance killer.

Additionally MySQL 5.0 (what we're using) has no support for indexing VIEWs, so every time a query is done it requires a full table scan to grab only 1 result!

However, there is hope.

I have never used this functionality in it, but PostgreSQL has support for materialized views. So, my understanding is that when you compute all your nasty subqueries it will keep the computed result in the VIEW until one of the underlying fields changes (tell me if I'm wrong here, Postgres devs / DBAs).

Additionally, we could not change to Postgres, so we made a cron file that builds our VIEWs only every 5 minutes and stores a statically computed version, making performance MUCH better. I think 10x better was the benchmark. However, this depends on if you're willing ti live with stale data for a while or not....

Answer 2

What is the client for these queries? If you are using any programming language (as opposed to say a thin web client), then you could just build the query in that language (perl/php example):

$stock_shortfall = "(required_stock - current_stock)";
$query = "SELECT
  description,
  unit_cost,
  current_stock,
  required_stock,

  $stock_shortfall                     AS stock_shortfall,
  $stock_shortfall * unit_cost         AS stock_shortfall_cost
FROM product_stock"

And if you might need to re-use this query frequently, then you could build a class around it or at least have a static library class of generated query strings.

Answer 3

Using FUNCTIONs might leverage maintainability... and hopefully (in the near future) it might boost performance too.

SELECT
  ...
  stock_shortfall(required_stock, current_stock),
  stock_shortfall_cost(required_stock, current_stock, unit_cost)
...

Those two functions should be marked as DETERMINISTIC. In MySql 5.0 that characteristic was not used by the optimizer. I don’t know if later versions already use it (so the “near future” reference).

Of course, simple single-used calculations rarely deserve the CREATE FUNCTION... typing overhead.

Answer 4

In the end, I wrote my SELECT statement similar to how I wrote it in my second example (the Desirable Solution) which could not be used to construct a VIEW.

I then wrote a small compiler to create from that statement a more complex query which could be used to construct the VIEW.

It is now much easier to maintain.

Thanks to Rob Van Dam for inspiring me to compile the query.