[development] Database / SQL future thoughts

[Mikkel Høgh]

On Wed, May 6, 2009 at 8:17 PM, larry at garfieldtech.com
<larry at garfieldtech.com> wrote:
> If MySQL had built-in materialized views, then we could push the hard work
> on to the database and it would do that sort of pre-generation itself.
>  Sadly it doesn't, so its "views" are really just syntax nicety.

Well, I don't know about MySQL, but in PostgreSQL there is actually a
slight performance gain from using database views, in that it shaves
off almost all the processing and query plan generation, so most of
the steps besides executing the actual query are skipped when using a
view.

That is a double-edge sword, however, since the query plan generated
for one set of parameters might not be all that suitable given another
set of parameters, so it'll have to be thought through carefully.

When you have a view, generating a materialised view can be achieved
with a bit of clever PL/pgSQL code.

Sadly, I don't currently have any Drupal sites under my care that
would warrant spending the time to do such things, but I find that
there can be a lot of improvements by just going "closer to the
metal".

Taking the query from Views.module, optimising it, and moving it into
a standard hook_menu page callback will also shave a bit of load off
both the frontend and the database server on heavily visited pages :)

-- 
Kind regards,
Mikkel Høgh