Skip to content

Instantly share code, notes, and snippets.

@miry
Last active March 19, 2021 07:51
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
Star You must be signed in to star a gist
Save miry/82daf5ae39c23087d142608137e0ba87 to your computer and use it in GitHub Desktop.
Example of Migration to create a table with cached results: Materialised view table
class MaterialisedView < ActiveRecord::Migration[5.2]
def change
execute <<-SQL
CREATE TABLE mv_complex_query
COMMENT 'Updated every 5m by the mv_complex_query event'
SELECT * FROM big_table
SQL
execute <<-SQL
CREATE EVENT dump_mv_complex_query
ON SCHEDULE EVERY 5 MINUTE
DO BEGIN
CREATE TABLE mv_complex_query_new
COMMENT 'Updated every 5m by the dump_mv_complex_query event'
SELECT * FROM big_table;
RENAME TABLE
mv_complex_query TO mv_complex_query_old,
mv_complex_query_new TO mv_complex_query;
DROP TABLE IF EXISTS mv_complex_query_old;
END
SQL
end
end
@miry
Copy link
Author

miry commented Mar 19, 2021

big_table could be any complex query or views

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment