This topic describes the customer stories where materialized views are used to reduce the latency consumed by customer queries.

Background information

Business advisor is an important product provided by Alibaba for tens of millions of merchants. Business advisor helps merchants analyze store operations at the earliest opportunity. Large amounts of data and traffic spikes increases the time required to analyze data and affects sales promotions.

Based on AnalyticDB for MySQL, business advisor provides hourly analysis capabilities that can be used during sales promotions. Business advisor displays the overall analysis of the three major indicators. These indicators include the number of products that are paid for, the number of buyers, the number of visitors, the number of people who add products to a cart, and the payment amount for purchases from stores, products and channels. Business advisor shows the ranking based on the industry and location. Business advisor shows the ranking trend of the store within a day and the changes in ranking throughout the day.

Business effects

If a frontend application performs a query on data every hour, AnalyticDB for MySQL computes the data per hour in real time. These calculations include complex operations such as JOIN and GROUP BY and take at least 1 second. It requires 24 seconds to display the data of the previous 24 hours. This latency is unacceptable.

Materialized views can be used to reduce this latency. If you store hourly presentation results in materialized views, you need only to query only materialized views. This reduces the time consumed by each query to 100 milliseconds.