Materialized views with MySQL

Emulated materialized views with MySQL has goo开发者_运维知识库d performance? I'm learning how to do with this link

thanks

Correction: "Materialized views" to "Emulated materialized views".

Flexviews (http://www.leapdb.com) is an open source PHP/MySQL based project. Flexviews adds incrementally refreshable materialized views (like the materialized views in Oracle) to MySQL, usng PHP and stored procedures.

It includes FlexCDC, a PHP based change data capture utility which reads binary logs, and the Flexviews MySQL stored procedures which are used to define and maintain the views.

Flexviews supports joins (inner join only) and aggregation so it can be used to create summary tables. Moreover, you can use Flexviews in combination with Mondrian's (a ROLAP server) aggregation designer to create summary tables that the ROLAP tool can automatically use.

NOTE: Flexviews is no longer maintained as an open source project. It was ported to LeapDB and now supports CREATE MATERIALIZED VIEW and other syntax for native materialized views. LeapDB also includes the WARP storage engine, a columnar storage engine with automatic bitmap indexing and star schema optimization.

MySQL doesn't have materialized views - the link just creates a table and stuffs data into it so the table can be indexed. That means the performance is par with a normal table, but you also have the overhead of flushing & repopulating the table (including indexes).

I didn't see what engine the table was using, but MEMORY would likely be a better choice.

A materialized view is just a fancy name for a normal table with the data from some heavy query.

So although creating it is just as heavy as the heavy query itself, querying it is really fast.

The big question here is how you want to update the view.

• You can do a regular full refresh. Simple to do, but heavy during that update and in between updates the data is outdated.
• You can use triggers to automatically update the data when inserting/deleting/updating. That makes the inserts/deletes/updates for your other tables slightly heavier, but it won't be outdated.