?

Log in

No account? Create an account

Drizzle, Views And Triggers

« previous entry | next entry »
Jul. 15th, 2009 | 01:15 pm

I've been trading twitter responses back and forth with jswanhart of Kickfire recently on the state and planning of Views in Drizzle.

In Drizzle right now we do not have views. There are plans to add views which never "materialize", but that is still a couple of milestones off.

One of the problems when talking about views is that the word "materialize" has been over used.

To "materialize" a view, means that you take the view definition, turn it into a temporary table, and then join it against a query. In Drizzle we consider this a "no no".

Why? Because any query that does this is more then likely going to turn out to be to slow to be of any real use in our domain space. The same problem exists with subqueries though for the time being we are leaving them in. Subqueries at this point are only semi-useful. Many of the common methods of using them result in a materialized table, which just makes them too slow. Our plans involve eliminating these sorts of queries and only allowing ones which can be "merged".

This will be limiting, but experience shows that the current state of use is that most users don't want to be able to write a query that will under perform (unless I am wrong, Google has disabled the subquery system in their version of MySQL just to make sure no developer gets anywhere near this part of the system).

Frankly, I would like to take this one step further at some point, and eliminate cartesian joins unless they are explicitly requested. How often do you really want one? Pretty much never. Typically they are done by accident and you either wait out the mistake or start hitting "CTRL-C" hoping to kill the query before it eats up the machine.

So "materializing" is out, but what is not out is "materialized views". This is an SQL feature whereby a table is created and is updated based on entries from one or more tables, either syncronously or asycronously. This is something I do expect us to support at some point.

One of the strengths in the current design is that the replication system lives as a service to the micro-kernel.

What does this mean?

It means that we internally have triggers on any sort of DDL/DML event that occurs in the server. As soon as someone wants to write up some parser goodness we can have basic triggers in whatever language you want.

We also gain materialized views via the same method. The "tires" on this project have been kicked around but no one has started it yet.

Having everything built on the same entry point to the micro-kernel really simplifies the design of a lot of different components. We have someone doing the design on direct to memcached replication for instance (we will have UDF as well, but this will simplify cache coherency and not require you to modify any SQL to make it work).

Materialized Views are pretty cool, materializing a view though pretty much make you want to cry.


BTW It was just pointed out to me that Justin had some of his own thoughts about this.

Link | Leave a comment |

Comments {26}

Re: Views - Sine qua non

from: jaypipes.myopenid.com
date: Jul. 16th, 2009 05:13 pm (UTC)
Link

PeterG?

Don't know many others who would use the term Sine qua non so glibly... :)

In any case, views are indeed useful, typically for a DBA or reporting-type db user who doesn't care to repeat typing in very long or complex statements. However, views which degrade performance of the server don't belong in the core. In Drizzle, they will be built and enabled as a rewriter plugin which should not effect environments which do not need them.

Cheers!

Jay

Reply | Parent | Thread

Brian "Krow" Aker

Re: Views - Sine qua non

from: krow
date: Jul. 16th, 2009 05:40 pm (UTC)
Link

The part I strongly believe about views, and their value, is in the ability to encapsulate underlying changes to table structure/name. That has always been to me the single most useful feature they provide.

Reply | Parent | Thread