Materialized Views and Column Storage with MySQL
LeapDB's cloud based MySQL add-on adds incrementally refreshable materialized views to MySQL 8.
LeapDB's MySQL compatible column storage engine called WARP enables MySQL to handle analytics queries on larger data sets than InnoDB.
Eliminate slow queries and slow dashboards/reports.
100% compatible with MySQL with added features
This is an example of creating a materialized view for a dashboard style query. This is query 4.1 of the Star Schema Benchmark. The data set is scale factor 30, or approximately 22GB of data in the 'lineorder' table. The database is configured with a 12GB innodb_buffer_pool_size.
The query initially takes over 11 minutes. Once the materialized view is created, access to the view is instant (35 rows). After modifying 50,000 rows, it takes only 7.31 seconds to update the view to reflect those changes. This means it takes only 7.31 seconds to run the query now, instead of 11.5 minutes! Refresh times are proportional to the numbers of underlying rows changed in the tables that define the view.
The query is now 100X faster!
WARP columnar storage engine
The following query is being executed over scale factor 400, which is 276GB. This is significantly larger than the size of the above example used for the materialized view, which was being executed over InnoDB data. InnoDB at 22GB of data took 11 minutes. In this example, WARP queries 13X the data (276GB) yet is still 3X faster than the InnoDB query on 22GB of data! This example used a 32 core machine with 64GB of ram.
When using the WARP storage engine, query parallelization and columnar storage improve performance significantly.
The LeapDB AWS marketplace AMI adds materialized views and columnar storage in the AWS cloud
Experience incrementally updateable materialized views, on demand table level replication and transformation, and columnar storage.
Materialized views are excellent for dashboards and reporting servers. Columnar storage option for ad-hoc querying of large data marts.