This topic describes a customer story where materialized views are used to reduce the response latency of customer queries.
Background information
Business advisor is an important service provided by Alibaba for tens of millions of merchants. Business advisor helps merchants analyze store operations at the earliest opportunity. During sales promotions, a large amount of data is generated and traffic spikes occur. Data analysis is especially significant in such scenarios.
Business advisor provides hourly analysis capabilities based on AnalyticDB for MySQL. It can be used during sales promotions. Business advisor displays an overall analysis of five major metrics, which include the number of products sold, the number of buyers, the number of visitors, the number of people who add products to the shopping cart, and the payment amount. The data of the metrics is generated from stores, products, and channels. Business advisor shows the ranking of a merchant based on the industry and location. Business advisor shows the ranking trend of a 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 performs calculations on the hourly data 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 longer than expected.
Materialized views can be used to reduce this latency. If you store hourly presentation results in materialized views, you need only to query materialized views. This reduces the time consumed by each query to 100 milliseconds.