Refreshing the Materialized View on demand can be implemented with a Stored Procedure as follows: DROP PROCEDURE refresh_mv_now This would cover the refreshment mode "NEVER" But this is not what we generally want to do. | product_name | price_sum | amount_sum | price_avg | amount_avg | sales_cnt | This is up to now the easiest part! And, as expected we get the correct result: mysql> SELECT * FROM sales_mv , AVG(product_price), AVG(product_amount) , SUM(product_price), SUM(product_amount) On such a small table it is pretty fast but when you have hundreds of products and millions of sales transactions it can take minutes to hours! Create your own Materialized View: DROP TABLE sales_mv | SIMPLE | sales | ALL | NULL | 6 | Using temporary Using filesort | | select_type | table | type | possible_keys | rows | Extra | , AVG(product_price) AS price_avg, AVG(product_amount) amount_agg , SUM(product_price) AS price_sum, SUM(product_amount) AS amount_sum , (NULL, 'Pear', 12.20, 4), (NULL, 'Plum', 4.85, 3)Īnd now we want to know the price sold and the money earned per product: EXPLAIN Sales_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY Assume we have a table sales: CREATE TABLE sales ( To understand all this more in detail it is probably easiest to do some examples. Also some snapshots or time delayed states can be produced: on demand (for example once a day, for example after nightly load)Ī refresh can be done in the following ways:īy storing the change information in a log table.never (only once in the beginning, for static data only).Materialized Views can be refreshed in different kinds. But this information can be up to 20% wrong! SELECT table_schema, table_name, table_rows Id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEYĭepending on the needed correctness of this information the table can be refreshed once a day (least used resources on the system but biggest error in result), once an hour or in most extreme case after every change (slowest)!Īn other possibility would be to get the data from the information schema. The following query can take some seconds up to minutes: SELECT COUNT(*)Ī possible solution for this would be to create a table where all InnoDB row counts are stored in CREATE TABLE innodb_row_count (
Returns immediate result because the counter is stored in the table header. Implement your own Materialized ViewsĪ short example for how this could be done is the following query: SELECT COUNT(*) But it is easy to build Materialized Views yourself. MySQL does not provide Materialized Views by itself. It depends on the requirements how often a Materialized View is refreshed and how actual its content is.īasically a Materialized View can be refreshed immediately or deferred, it can be refreshed fully or to a certain point in time. Materialized Views have to be refreshed once in a while. Materialized Views are used when immediate response is needed and the query where the Materialized View bases on would take to long to produce a result. Unlike a simple VIEW the result of a Materialized View is stored somewhere, generally in a table. Some performance benchmarks for our Materialized Views:Ī Materialized View (MV) is the pre-calculated (materialized) result of a query.Materialized Views with snapshotting functionality.
0 kommentar(er)
