Introduction to Materialized Views

Chado is efficient as a data warehouse but queries can become slow depending on the number of table joins and amount of data. To help simplify and speed these queries, materialized views can be employed. For a materialized view, a table is created and then populated with the results of a pre-defined SQL query. Therefore, rather than execute the pre-defined query which may take a long time, the query on the materialized view is more simple and faster. A side effect, however is redundant data, with the materialized view becoming stale if not updated regularly. 

Tripal provides a mechanism for populating and updating these materialized views. These can be found on the TripalChado SchemaMaterialized Views page.

Tripal2.0 mviews.png

Here we see several materialized views. These were installed automatically by the various Tripal modules. To update these views, click the Populate button for each one.

This will submit jobs to populate the views with data. Now, run the jobs:

cd /var/www/html
drush trp-run-jobs --user=administrator

You can now see that all views are up-to-date on the Materialized Views Page. The number of rows in the view table is shown.