Problem Statement
A report joins a large fact table to three dimensions and runs slowly. Outline a tuning checklist focused on indexes and join order.
Explanation
First, ensure foreign keys from the fact to each dimension are indexed. Add composite indexes that start with the join key and include common filters for selective access. Next, verify that the dimensions have primary or unique indexes on their keys for quick lookups.
Run EXPLAIN to see join order and row estimates. Push selective filters into the earliest steps, and consider pre-aggregating the fact in a CTE or temp table if the report only needs rolled-up data. Validate statistics, and test alternative join orders by adding selective predicates or hints only as a last resort.
Code Solution
SolutionRead Only
CREATE INDEX idx_fact_date ON fact_sales(date_id); CREATE INDEX idx_fact_customer ON fact_sales(customer_id); -- Verify unique indexes on dim_date(date_id), dim_customer(customer_id)
Practice Sets
This question appears in the following practice sets:
