Glotzfisch.de

As mySQL does not support materialised views out of the box, I was looking for a work around. The most convincing one I found (because it only switches tables when the new one is readily filled) is:

1. create your view v_name as needed.
2. create a table having the same fields as your view and name it e.g. mview_name
3. create a procedure to refill the table

CREATE PROCEDURE update_mview_name ()
BEGIN
  DROP TABLE IF EXISTS new_mview_name;
  CREATE TABLE new_mview_name AS SELECT * from v_name;
  RENAME TABLE mview_name TO old_mview_name, new_mview_name TO mview_name;
  DROP TABLE IF EXISTS old_mview_name;
END;

This entry was posted on Thursday, 18. February 2010 at 15:37 and is filed under mySQL. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback (Guckstu hier) from your own site.