MySQL Forums :: Performance :: Millions of rows, storing aggregate/rollup results help please


Advanced Search

Re: Millions of rows, storing aggregate/rollup results help please
Posted by: Jacqueline Jacobin ()
Date: August 31, 2016 07:03AM

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
;

Options: ReplyQuote


Subject Views Written By Posted
Millions of rows, storing aggregate/rollup results help please 545 Jacqueline Jacobin 08/28/2016 12:23PM
Re: Millions of rows, storing aggregate/rollup results help please 367 Rick James 08/30/2016 02:47PM
Re: Millions of rows, storing aggregate/rollup results help please 282 Jacqueline Jacobin 08/31/2016 07:03AM
Re: Millions of rows, storing aggregate/rollup results help please 269 Rick James 09/01/2016 04:35PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.