The WARP storage engine provides columnar storage, automatic bitmap indexing and parallel query with star schema optimization
In addition to excellent materialized view support, for ad hoc querying of large amounts of data, particularly in a star schema, LeapDB provides the WARP storage engine. The WARP storage engine is a columnar storage engine, which reduces IO significantly for queries that access only a small number of columns in a row. This allows for tables with very wide rows, something that traditional row stores struggle with.
Additionally, IO is mostly sequential, which gives much better query performance, as sequential IO is generally significantly faster than random IO. For example, the WARP storage engine is 100X faster than InnoDB on many queries on the "star schema benchmark", an industry standard benchmark that uses the data from the TPC-H<tm> benchmark structured as a star schema.
The WARP storage engine supports bitmap indexing, and it automatically indexes any column used in a WHERE clause. Bitmap indexes can be used to quickly answer many queries which b-tree indexes can not answer, even with multiple range conditions! Such queries are not easily answerable by b-tree indexes. WARP is excellent at 'needle in the haystack' type queries. In WARP, query times are generally proportional to the number of hits in the bitmap indexes. Automatic indexing takes away the headaches of having to manually add and tune indexes for your database workloads. This automatic bitmap indexing is accomplished using the "engine condition pushdown" feature of the MySQL storage engine interface.
Star schema optimization is included in the WARP storage engine. This includes query parallelism in the join process when filters are placed on dimension tables. This query parallelism can increase performance dramatically on computers with multiple cores. The following chart includes all the queries of the "star schema benchmark" where WARP is compared to InnoDB. InnoDB has been tuned to disable the adaptive hash index (see MySQL bug 106712).
The InnoDB schema includes indexes for executing the queries. This benchmark is at scale factor 30, with approximately 200M rows in the fact table which is 20GB in size. The InnoDB buffer pool size was configured at 30GB as was the WARP cache size. The benchmark is run on a desktop processor with 12 2.2GHz cores available for query processing.
WARP is the only columnar storage engine (column store) available for MySQL 8. The WARP engine is open source and support and is available through LeapDB consulting and support services.
The WARP storage engine is considered BETA. Problems with the WARP storage engine can be reported to email@example.com.
LeapDB includes "star schema optimization" which is a technique for improving query performance on star schema queries that filter out rows based on filters in dimension tables. This reduces IO for star schema queries and provides opportunities for query parallelization.
I use my benchmark queries on the BTS Ontime data set in a materialized view context and compare performance of the queries when using the materialized view against querying the base table.