Re: Millions of rows, storing aggregate/rollup results help please

[Jacqueline Jacobin]

Thank you Rick. Here is my CREATE statement, with some names changed for security reasons. Note a typical query will be something like:

SELECT COUNT(*) FROM mytable
WHERE Year IN (2005, 2015)
AND Filter1 IN (4)
AND Filter2 IN (1,2,3)
AND Filter3 IN (1)
GROUP BY Year

So thats why I'm really confused about best summary table, because the possibilities seem endless for every permutation of every dimension. Note that aside from Filter1 and Filter2, every other table has only 0,1, or NULL.


CREATE TABLE `mytable` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`Year` YEAR NOT NULL,
`State` TINYINT(2) NULL DEFAULT NULL,
`Agency` TINYINT(1) NULL DEFAULT NULL,
`Filter1` TINYINT(1) NULL DEFAULT NULL,
`Filter2` TINYINT(1) NULL DEFAULT NULL,
`Filter3` TINYINT(1) NULL DEFAULT NULL,
`Filter4` TINYINT(1) NULL DEFAULT NULL,
`Filter5` TINYINT(1) NULL DEFAULT NULL,
`Filter6` TINYINT(1) NULL DEFAULT NULL,
`Filter7` TINYINT(1) NULL DEFAULT NULL,
`Filter8` TINYINT(1) NULL DEFAULT NULL,
PRIMARY KEY (`id`),
INDEX `Year` (`Year`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
ROW_FORMAT=COMPACT
AUTO_INCREMENT=6577333
;