Summary Tables in MySQL

Summary Tables for Data Warehouse "Reports"

General structure of a Summary Table

   FLOOR(UNIX_TIMESTAMP(dt) / 3600)
   FROM_UNIXTIME(hour * 3600)

Example

   PRIMARY KEY(city, datetime),
   Aggregations: ct, sum_price

   # Core of INSERT..SELECT:
   DATE(datetime) AS date, city, COUNT(*) AS ct, SUM(price) AS sum_price

   # Reporting average price for last month, broken down by city:
   SELECT city,
          SUM(sum_price) / SUM(ct) AS 'AveragePrice'
      FROM SalesSummary
      WHERE datetime BETWEEN ...
      GROUP BY city;

   # Monthly sales, nationwide, from same summary table:
   SELECT MONTH(datetime) AS 'Month',
          SUM(ct)         AS 'TotalSalesCount'
          SUM(sum_price)  AS 'TotalDollars'
      FROM SalesSummary
      WHERE datetime BETWEEN ...
      GROUP BY MONTH(datetime);
   # This might benefit from a secondary INDEX(datetime)

When to augment the Summary Table(s)?

Summarizing while Inserting (one row at a time)

    INSERT INTO Fact ...;
    INSERT INTO Summary (..., ct, foo, ...) VALUES (..., 1, foo, ...)
        ON DUPLICATE KEY UPDATE ct = ct+1, sum_foo = sum_foo + VALUES(foo), ...;

Summarizing Periodically vs as-needed

Summarizing while Batch Inserting

   FROM Fact
   WHERE id BETWEEN min_id and max_id

Summarizing when using a Staging Table

Summary Table: PK or not?

   id INT UNSIGNED AUTO_INCREMENT NOT NULL,
   ...
   PRIMARY KEY(foo, dy, id),  -- `id` added to make unique
   INDEX(id)                  -- sufficient to keep AUTO_INCREMENT happy

Averages, etc.

    SQRT( SUM(sum_foo2)/SUM(ct) - POWER(SUM(sum_foo)/SUM(ct), 2) )

Staging Table

Extreme Design

"Left Off"

Flip-Flop Staging

    # Prep for flip:
    CREATE TABLE new LIKE Staging;

    # Swap (flip) Staging tables:
    RENAME TABLE Staging TO old, new TO Staging;

    # Normalize new `foo`s:
    # (autocommit = 1)
    INSERT IGNORE INTO Foos SELECT fpp FROM old LEFT JOIN Foos ...

    # Prep for possible deadlocks, etc
    while...
    START TRANSACTION;

    # Add to Fact:
    INSERT INTO Fact ... FROM old JOIN Foos ...

    # Summarize:
    INSERT INTO Summary ... FROM old ... GROUP BY ...

    COMMIT;
    end-while

    # Cleanup:
    DROP TABLE old;

Multiple Summary Tables

    1. WHERE make = ? AND model_year = ?  GROUP BY service_date, service_type
    2. WHERE make = ? AND model = ?       GROUP BY service_date, service_type
    3. WHERE service_type = ?             GROUP BY make, model, service_date
    4. WHERE service_date between ? and ? GROUP BY make, model, model_year

Games on Summary Tables

References

Postlog