Category / Section
Creating a Summary Table in MySQL
Published:
What is a summary table?
A summary table is a precomputed table that contains aggregated data from one or more source tables. It’s utilized to enhance query performance by storing summarized data that can be quickly accessed without the need for complex calculations every time the data is queried.
When to use the summary table
Summary tables are used when:
- There is a need to improve query performance by reducing the computational load on the database server.
- Frequently accessed data needs to be aggregated, summarized, and presented in a concise format.
- Complex queries involving large datasets can benefit from precomputed summary data.
Points to consider on creating summary tables
When creating summary tables, consider the following points:
- Choose the appropriate granularity for summarization (e.g., daily, weekly, monthly).
- Ensure that the summary table accurately reflects the source data.
- Determine the appropriate aggregation functions (e.g., SUM, AVG, COUNT) based on the analysis requirements.
- Refresh or update the summary table periodically to reflect changes in the source data.
- Consider indexing columns in the summary table to further enhance query performance.
- Balance the trade-off between storage space and query performance when deciding which data to summarize.
Example of creating a summary table in MySQL
Consider we have the following table named “marketing_details” in our MySQL database.
CREATE TABLE `marketing_details` (
`date` timestamp(6) NULL DEFAULT NULL,
`destinationurl` varchar(500) DEFAULT NULL,
`adcost` int DEFAULT NULL,
`visitors` int DEFAULT NULL,
`visitors_after_landing_page` int DEFAULT NULL,
`sessions` int DEFAULT NULL,
`impressions` int DEFAULT NULL,
`clicks` int DEFAULT NULL,
`revenue` int DEFAULT NULL,
`expense` int DEFAULT NULL,
`channel_id` int DEFAULT NULL,
`channel_name` varchar(100) DEFAULT NULL,
`product_used` int DEFAULT NULL,
`leads` int DEFAULT NULL,
`wons` int DEFAULT NULL
);
Now, create the summary table with the required pre-aggregated fields and calculated fields:
CREATE TABLE marketing_details_mv (
month_year VARCHAR(20),
destinationurl VARCHAR(500),
channel_name VARCHAR(100),
total_unique_destinations INT,
total_adcost INT,
average_adcost DECIMAL(10, 2),
total_visitors INT,
total_visitors_after_landing_page INT,
total_sessions INT,
total_impressions INT,
total_clicks INT,
total_revenue INT,
average_revenue DECIMAL(10, 2),
total_expense INT,
average_expense DECIMAL(10, 2),
total_unique_channel_ids INT,
total_unique_channels INT,
total_unique_products_used INT,
total_leads INT,
average_leads DECIMAL(10, 2),
total_wons INT,
average_wons DECIMAL(10, 2),
conversion_rate DECIMAL(10, 2),
click_through_rate DECIMAL(10, 2),
cost_per_click DECIMAL(10, 2),
return_on_investment DECIMAL(10, 2),
average_session_duration DECIMAL(10, 2),
lead_conversion_rate DECIMAL(10, 2),
average_revenue_per_visitor DECIMAL(10, 2),
channel_engagement_rate DECIMAL(10, 2),
PRIMARY KEY (month_year, destinationurl, channel_name)
);
Populate the summary table with the data from the raw table.
INSERT INTO marketing_details_mv
SELECT
DATE_FORMAT(date, '%b %Y') AS month_year,
destinationurl,
channel_name,
COUNT(DISTINCT destinationurl) AS total_unique_destinations,
SUM(adcost) AS total_adcost,
AVG(adcost) AS average_adcost,
SUM(visitors) AS total_visitors,
SUM(visitors_after_landing_page) AS total_visitors_after_landing_page,
SUM(sessions) AS total_sessions,
SUM(impressions) AS total_impressions,
SUM(clicks) AS total_clicks,
SUM(revenue) AS total_revenue,
AVG(revenue) AS average_revenue,
SUM(expense) AS total_expense,
AVG(expense) AS average_expense,
COUNT(DISTINCT channel_id) AS total_unique_channel_ids,
COUNT(DISTINCT channel_name) AS total_unique_channels,
COUNT(DISTINCT product_used) AS total_unique_products_used,
SUM(leads) AS total_leads,
AVG(leads) AS average_leads,
SUM(wons) AS total_wons,
AVG(wons) AS average_wons,
(SUM(leads) / SUM(visitors)) AS conversion_rate,
(SUM(clicks) / SUM(impressions)) AS click_through_rate,
(SUM(adcost) / NULLIF(SUM(clicks), 0)) AS cost_per_click,
(SUM(revenue) / NULLIF(SUM(expense), 0)) AS return_on_investment,
(SUM(sessions) / NULLIF(SUM(visitors), 0)) AS average_session_duration,
(SUM(wons) / NULLIF(SUM(leads), 0)) AS lead_conversion_rate,
(SUM(revenue) / NULLIF(SUM(visitors), 0)) AS average_revenue_per_visitor,
(SUM(clicks + leads + wons) / NULLIF(SUM(impressions), 0)) AS channel_engagement_rate
FROM marketing_details
GROUP BY month_year, destinationurl, channel_name;
Optimizing Dashboard Performance
Please consult the knowledge base article titled “Materialized Views in MySQL: Optimizing Dashboard Performance” for information on enhancing dashboard performance through the use of summary tables.
References
VS
