Emulated materialized views with MySQL has good performance? I'm learning how to do with this link
thanks
Correction: "Materialized views" to "Emulated materialized views".
Emulated materialized views with MySQL has good performance? I'm learning how to do with this link
thanks
Correction: "Materialized views" to "Emulated materialized views".
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.
Flexviews (http://flexvie.ws) 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.